How to do SELECT from mutliple backend files ..

[edmscan]

I am running into a problem and have not been able to find a solution.



I have a accdb file that is going to hit the 2 GB limit soon and need to make a separate backend file. (1 front end file and 2 backend files) This is fine .. done that already.

The big ? is .. I know I can use a Union query, that works but the sorting of the data is not the same as a normal select that I can tell.

So .. can you make a SELECT from 2 separate back end tables ? and be able to sort as you do from one table. Or is the only way .. a union query with 2 selects, 1 for each table.

I would prefer not to do a Union but I think I have no choice. The issue there "is the sorting" not so much as getting the data, that part seems fine.

The data is exactly the same in both tables. Just one table has older data .. the 2nd backend file, newer data.

Here is a scenario ... and looking for suggestions.

Table 1 (Fruit Sales 2014) Data in one backend table.
APPLES JAN 2014 78
APPLES FEB 2014 54
APPLES MAR 2014 32
APPLES JUNE 2014 176
ORANGES JULY 2014 122
ORANGES AUGUST 2014 111

TABLE 2 (Fruit Sales 2015) Data in another backend table.

APPLES JAN 2015 343
APPLES FEB 2015 34
ORANGES JAN 2015 343
ORANGES FEB 2015 23

I would like my result to be ... (Sorted by product asc, then count desc)

APPLES JAN 2015 343
APPLES JUNE 2014 176
APPLES JAN 2014 78
APPLES FEB 2014 54
APPLES FEB 2015 34
APPLES MAR 2014 32
ORANGES JAN 2015 343
ORANGES JULY 2014 122
ORANGES AUGUST 2014 111
ORANGES FEB 2015 23

As long as the data is in one table .. no problem. But it is getting the result from the 2 separate tables and having the sort correct.

Looking for suggestions.

Thanks
- Kevin -

[Rawb]

The only way I know of is to use a UNION query or custom VBA code.

If you include an ORDER BY clause at the end of your query, it should sort the results from both Tables together. If that doesn't work though, you can always create two Saved Queries: One that only does the UNION (without sorting), and another only does the sorting.

[edmscan]

The only way I know of is to use a UNION query or custom VBA code.

If you include an ORDER BY clause at the end of your query, it should sort the results from both Tables together. If that doesn't work though, you can always create two Saved Queries: One that only does the UNION (without sorting), and another only does the sorting.

Tried that. The data is all there, but the sort on the 2nd query does not work.

Here is my select .. from the 2nd query.

SELECT Query1.Radio, Query1.User, Query1.Confirmed, Query1.Type, Query1.[Trans Type], Query1.Group, Query1.Name, Query1.Count
FROM Query1
ORDER BY Query1.Type ASC, Query1.Count DESC;

I tried to insert an image of my result .. it is not working on 2 different browsers, but it is not doing the Count DESC part.


Thanks ..
- Kevin -

[Rawb]

You shouldn't use Name as a Column name since it's a reserved word in Access. If you can, try changing the name of that Column to something else and see if that solves your problem. Other than that, I don't see anything wrong with what you have. From what I can see, it should be working for you...

If you can't change the name, you can try putting it in square brackets. In fact, I make it a standard practice to always put a Column name in square brackets to help prevent confusion:

SELECT Query1.[Radio], Query1.[User], Query1.[Confirmed], Query1.[Type], Query1.[Trans Type], Query1.[Group], Query1.[Name], Query1.[Count]
FROM Query1
ORDER BY Query1.[Type] ASC, Query1.[Count] DESC;

What does your first Query look like? I doubt the issue is in there, but I suppose it's possible.

[edmscan]

You shouldn't use Name as a Column name since it's a reserved word in Access. If you can, try changing the name of that Column to something else and see if that solves your problem. Other than that, I don't see anything wrong with what you have. From what I can see, it should be working for you...

If you can't change the name, you can try putting it in square brackets. In fact, I make it a standard practice to always put a Column name in square brackets to help prevent confusion:

SELECT Query1.[Radio], Query1.[User], Query1.[Confirmed], Query1.[Type], Query1.[Trans Type], Query1.[Group], Query1.[Name], Query1.[Count]
FROM Query1
ORDER BY Query1.[Type] ASC, Query1.[Count] DESC;

What does your first Query look like? I doubt the issue is in there, but I suppose it's possible.

I resolved it .. it works but the best solution, who knows.

I did a make table query to get the results from the first query into there. It is really just a temp table and for this purpose it works. The one thing I did notice .. is that it was probably not sorting right cause it thought it was a text field and not a integer or similar. Fixed that and it works great now.

Then I made the 2nd query (just a normal select) to get the results I need from that table.

I did not know about 'Name" .. but at this time, don't know if I can change it and it has never caused me any problems.

[ssanfu]

"User", "Type", "Group", and "Count" are also reserved words in Access. Also you should not use punctuation, special characters (except the underscore) or spaces in object names.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html

Enclosing the reserved words in brackets ([]) tells access that the words are not reserved words, BUT... even though you haven't had any problems (yet), you can. I have heard of databases running for years, then just stop working due to object names.

Kinda like your hard drive; it's not IF the hard drive will fail, it's WHEN.

I have a accdb file that is going to hit the 2 GB limit soon

Interesting...... my question is: WHY is the BE getting close to 2 Gb?

Are you regularly doing "Compact and Repair" on the BE?
How big is the (un-split) table "Fruit Sales"?

You can have 1 BE for each table. Then each table can be 2GB.
Since you link the tables in the BEs to the FE, from the FE, it doesn't appear any different than having all of the tables in one BE.

[edmscan]

"User", "Type", "Group", and "Count" are also reserved words in Access. Also you should not use punctuation, special characters (except the underscore) or spaces in object names.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html

Enclosing the reserved words in brackets ([]) tells access that the words are not reserved words, BUT... even though you haven't had any problems (yet), you can. I have heard of databases running for years, then just stop working due to object names.

Kinda like your hard drive; it's not IF the hard drive will fail, it's WHEN.



Interesting...... my question is: WHY is the BE getting close to 2 Gb?

Are you regularly doing "Compact and Repair" on the BE?
How big is the (un-split) table "Fruit Sales"?

You can have 1 BE for each table. Then each table can be 2GB.
Since you link the tables in the BEs to the FE, from the FE, it doesn't appear any different than having all of the tables in one BE.

Ok .. well, thanks for letting me know about the reserved words. I obviously knew about them .. but never knew what they all were. However .. is there a way to fix ALL the instances of the reserved words at once .. in a quick manner. I can do the code easily .. but what about the queries, which are the main ones.

Re the size of the tables. I have like maybe 10 tables .. all of them are pretty small (from like 50 to max 8000 records depending on the purpose and none have too many fields) in all but ONE .. the big problem. This table is the cause of my issues, and it grows by about 60,000 to 70,000 records a day. That is why the BE is the size that it is. It is about 1.22 GB right now .. but if I do my import, it seems to BLOAT .. to over 2 GB. So I have to now cut the amount of records in that import to about 1/2 day (so max 35,000 records). Any more right now .. and it will freak. The table has about 14 million records in it now and is growing at near 2 million a month.

So .. I already have a 2nd backend file for the new data being collected. I am hoping that I do not need a 3rd. I only need to keep the system running til about June 2016.

That is the scoop .. oh and the fruit was just an example. I fixed the issue .. by putting the data from the union query (query1) into a temp table .. and then running a select (query2) against that. It works just fine. It was just way easier than trying to get the union query to sort. It is how I will do it going forward.

And yes .. I do a compact after my import.

[ssanfu]

You might start looking at SQL Server Express as a BE. The size limit in 2008 and later has been raised to 10 GB.

[edmscan]

You might start looking at SQL Server Express as a BE. The size limit in 2008 and later has been raised to 10 GB.

I looked at SQL Server Express. I abandoned the idea. It was just not a solution for me. I am going to use 5 BE files in Access instead. The good thing ... I never alter the data, just run queries against it. So .. I need to make some changes, but I have made all the backend files now and will just make all the needed FE changes all at once. I am not sure that I will need all 5 files, but it is easier for me to go and do it this way.