Select/Union query wants a field entered, but the Select/Union queries that make it up don't

[Tom Carp]

Have a simple Access db that has tables linked to individual Excel spreadsheets. The spreadsheets are accounting journals; all with the same structure and formatting.

SELECT/UNION queries are used to combine all the journal data. Three (q1table, q2table, and q3table) SELECT subsets of the linked tables, and a fourth (qCombined) selects q1table, q2table, and q3 table:

q1table
SELECT * from [table1]
UNION SELECT * from [table2];
q2table
SELECT * from [table3]
UNION SELECT * from [table4];
q3table
SELECT * from [table5]
UNION SELECT * from [table6];


qCombined
SELECT * from [q1table]
UNION SELECT * from [q2table]
UNION SELECT * from [q3table];

q1table, q2table, and q3table run just fine. However, qCombined asks for a field when run.

Any idea why?

[ranman256]

1 of the queries is missing a field or spelled it wrong.

[Tom Carp]

Thanks, but there are no fields specified in the queries. They're all SELECT *.

[Micron]

Can't see anything obvious. I'd combine as different pairs to see if it matters:

SELECT * from [q1table]
UNION SELECT * from [q3table];

SELECT * from [q1table]
UNION SELECT * from [q2table]

SELECT * from [q2table]
UNION SELECT * from [q3table];

and any others I might have missed. Also would try
SELECT * from [q1table]
SELECT * from [q2table]
SELECT * from [q3table]
since any stacked query runs the parent query; i.e SELECT * from [q1table] will run q1table query.

[Tom Carp]

Can't see anything obvious. I'd combine as different pairs to see if it matters:

SELECT * from [q1table]
UNION SELECT * from [q3table];

SELECT * from [q1table]
UNION SELECT * from [q2table]

SELECT * from [q2table]
UNION SELECT * from [q3table];

and any others I might have missed. Also would try
SELECT * from [q1table]
SELECT * from [q2table]
SELECT * from [q3table]
since any stacked query runs the parent query; i.e SELECT * from [q1table] will run q1table query.

Thank you very much. Based on your suggestions, I took a closer look at the data request. It references the third select (q3table) in the final union query (qCombined). Assuming Access doesn't sort query records before running (if it did, the "q3table" in the real system would sort to first in sequence), it seems to have successfully run q1table and q2table. I'll run some tests focused there.

Your suggestions also got me thinking about the design; i.e., why not just have one SELECT/UNION query that includes all the linked tables?

The actual system has 70 linked tables selected via 3 union queries. Those three union queries and combined via the final union query.

It's set up that way because I vaguely remember when putting the system together that I ran into a limit to the number of SELECT/UNION statements in one query. This is a simple accounting system so it was convenient to divide the tables (journals) up based on their status (i.e. current, inactive, archive).

What's interesting to me is why the three table queries run successfully, but the query that combines those queries asks for field data.

[Welshgasman]

What is the field it is asking for?

[Micron]

There is a limit of (I think) 50 UNION statements and includes those in queries that call other queries (aka stacked query). Having to do this at all is a sign of using tables that are not normalized, which is what it seemed would be the case as soon as you mentioned spreadsheets. However, I imagine you will persevere rather than create normalized tables?

[Tom Carp]

It's the first field (in this case "Account").

[June7]

Could have sworn I worked with someone who got stacked UNION to work when combined total of SELECT lines more than 50. Unfortunately, can't find it again.

I just tested and it fails.

[Micron]

It's the first field (in this case "Account").

Could be a misspelled domain or field name somewhere along the trail?

[Welshgasman]

Well, without seeing the tables, it will be one of those 'how long should my piece of string be?' queries.

[Tom Carp]

There is a limit of (I think) 50 UNION statements and includes those in queries that call other queries (aka stacked query). Having to do this at all is a sign of using tables that are not normalized, which is what it seemed would be the case as soon as you mentioned spreadsheets. However, I imagine you will persevere rather than create normalized tables?

As little as I understand normalization, it appears to be focused on relational db design, specifically to eliminate redundant data. For example, instead of having inventory records with each vendor source as a field, a separate Vendor table is created, and the inventory records changed to used a Vendor primary key. I suspect normalization may have even other aspects.

The way I'm using Access isn't because of it being a relational db. It's a way to bring non-Access data together, and then "shape" that data via queries. Those queries are then connected to via Excel workbooks that do the financial reporting.

I did experiment once with reporting and data entry via Access, but the learning curve too steep. Besides, this is home accounting so the bulk of the transaction data is downloaded from financial institutions. Those downloads would have to make their way to Access somehow.

I'm not sure how normalizing the tables deals with data that is in download formats (e.g. csv).

[Micron]

I'm not sure how normalizing the tables deals with data that is in download formats

Unless the csv layout is conducive to proper db design (and it seldom seems to be) then it won't - directly. You could link to the sheets then run queries to append/update relational tables from those sheets but in your case I imagine it's not worth the effort - until you get to a point where you reach some sort of maximum like 50 Union statements. Relational tables would likely eliminate the need for any Union queries.

[ssanfu]

I am curious to see the design of the tables.... any chance you would copy the dB, delete everything except the tables and queries (and delete the data in the tables if sensitive)?

[Tom Carp]

Thanks again for taking time with this, and I apologize for being a bit slow understanding the path to using a different approach to the journal data. I'm very willing to re-think the overall design, at a minimum just to add a little to my Access learning. This might be a good way to think through it. BTW, there are 70 Excel journals, so the 50 Union limit was hit, which is why there a multiple Union queries.

Of the 70 journals, 9 have the overwhelming bulk of new transactions that originate as downloads from financial institutions (in csv format). There are another 20 or so that have transactions but the volumes are low enough that there's no data download. I manually append that csv data via Copy/Paste to the corresponding formatted journal spreadsheets, and manually enter the other transactions data into their spreadsheets. During the process, each transaction is assigned a category, and additional information, like notes, might be added. There is some "normalization" being done in the Excel journals, e.g. the Account and Category fields are set up to select via pull-down, and others come from lookups. The result are journal spreadsheets, treated as the "authority" data.

Until all that's done, the journal transactions aren't completely entered, so it seems to make sense to stay in Excel until then.

I'll keep thinking about how to follow the advice to normalize in Access, but I'm not seeing it yet.