Best Way to Combine 13 Tables - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Best Way to Combine 13 Tables

Angela McCarthy

2 months ago

Hi all,

Sorry for the late Friday post, my brain is fried.

I need to combine info from 13 Tables that all work independently in wonderful fashion! They all have the same structure/field names. I thought the best way was to do a Union Query, but I am having no luck-error saying "Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." I took the example from the Work Order course for the NameSearchUnionQ and changed the names of my Tables and fields and added the ones I needed. I have checked all spelling and punctuation and is correct. The end result from this is to have totals that I can create a List Box from, I have been able to get the list to work for one of the tables but I need them all to feed in.

If this will not work then how do I combine the individual Queries that all work independently to make one query?

My statement repeats this:
SELECT
AccountID AS ID,
"ID" AS
TYPE,
Debit,
Credit,
FROM
[TableName];
UNION
(((Repeat for each Table)))

	Juan Rivera @Reply 2 months ago
just thinking apend query to new table and save the old. 1st back up....back up.... can never say back up too much V/r Juan

	Alex Hedley @Reply 2 months ago
Why is ID aliased to Type? That seems confusing.

	Alex Hedley @Reply 2 months ago
Glossary: Reserved Words

	Richard Rost @Reply 2 months ago
Are you sure you need a union query if this is something that would be better off in a temp table? Sometimes that works better. Do a series of append queries and build a temp table out of the data. I found that works a lot better than complex union queries. And yeah, like Alex said, watch your reserved words.

Raymond Spornhauer @Reply

2 months ago

You need to determine if the Tables you are trying to combine have related data. If you have AccountID in every table, then you need to make sure all the related data gets the new ID fields.

I would make a new table with a new Primary Key, include the old Primary Key as a Number field in the new Table.

Once you get your 13 tables combined, you'll need to use Update queries to change the old Foreign Keys to the new Foreign Keys.

-Raymond

Angela McCarthy OP @Reply

2 months ago

Thank you all for your response!

I have figured out what I need and have one last glitch I cannot get past.

Here is my SQL statement for my Union Query and this works exactly as I need. My glitch is that I need it to sort a different way than it is. If I add the AccountID or AccountType field I get the error "Your query does not include the specified expression 'AccountType' as a part of an aggregate function"- These are in the Queries before you ask. The ultimate goal is to get this Query into a Report that Sorts by one of these 2 fields (it currently sorts by name) and of course neither of these 2 fields show up to sort by.
SELECT
    [CheckRegQ].AccountName,
    Sum([CheckRegQ].[Amount]) AS Total
FROM
    [CheckRegQ]
GROUP BY
    [CheckRegQ].AccountName;

UNION
SELECT
    [AcctsRecQ].AccountName,
    Sum([AcctsRecQ].Amount) AS Total
FROM
    [AcctsRecQ]
GROUP BY
    [AcctsRecQ].AccountName
UNION (With more after this)

	Richard Rost @Reply 2 months ago
I will talk about this in Friday's Quick Queries video: 599cd.com/QQ81

Angela McCarthy OP @Reply

2 months ago

Thank you so very much Richard!

Once I figured out how to listen to you I got my things all cleared up. I might be close to have my Dual Entry Accounting figured out.

Now to go back in and figure out what I do not need to keep... I keep everything until I get to the end. Probably the wrong way for you but it is the way I think.

I think we can close this thread, at least from my perspective.

Happy Weekend!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View