A Very Odd Querying Problem

[Mithander]

I don't quite know the proper terms as I've only been using access for the past month or so. I will try to explain what I'm doing in the best way possible.
I have 4 queries, all of which are based off of, in total, 6 tables. In order to make the structure understandable, I'll try to explain how they're structured. Each of the 4 queries have one exclusive table that they're using and they all share two table. My problem does not have to deal with these per say, but it might help if I explain this part because of what I'm trying to do.
Now I'm trying to make a query that queries my other 4 queries (yes I do understand that is confusing but I'm trying my best to explain it). With these 4 queries they share a similar column name called Part Name, which all share some similar names. To each of these Part Names, there's also another column called Number Needed.
What I'm trying to do is to make a query that can sort through the Part Names and show them without any copies and for the ones that were copies, add the Numbers Needed so it shows the total of any former duplicates in the Part Name.
Sorry if this was kinda confusing, but I really need help ASAP because the order for these parts need to be placed soon. If you can help me please do, and if you have any questions of clarification please ask.
Thank you very much.

ALSO, I know nothing about SQL, so anything concerning it, will not really be understood by me.

[MSAccessRookie]

A UNION query comes to mind. Something Like: (you tailor to your needs)

Select PartName, NumberNeeded, {whatever else you need}
FROM ((PartTable1 INNER JOIN PartTable5 ON {whatever they join on})
INNER JOIN PartTable6 ON {whatever they join on})
UNION
Select PartName, NumberNeeded, {whatever else you need}
FROM ((PartTable2 INNER JOIN PartTable5 ON {whatever they join on})
INNER JOIN PartTable6 ON {whatever they join on})
UNION
Select PartName, NumberNeeded, {whatever else you need}
FROM ((PartTable3 INNER JOIN PartTable5 ON {whatever they join on})
INNER JOIN PartTable6 ON {whatever they join on})
UNION
Select PartName, NumberNeeded, {whatever else you need}
FROM ((PartTable4 INNER JOIN PartTable5 ON {whatever they join on})
INNER JOIN PartTable6 ON {whatever they join on})

This will get you a combined list and remove duplicates for you. If you want a complete list to remove the duplicates on your own, substitute UNION ALL for UNION

[Mithander]

Dear MSAccessRookie,
Thank you very much for your help. However I know nothing about the scripting functions of Access so I do not know how to utilize what you wrote up above... Thanks for the help though. If anyone can give me a step by step via the GUI in Access 97, it would be greatly appreciated.

[georgedwilkinson]

Well, you obviously know how to make/review queries since you mention them in your post. Just create a new query, don't select any tables, go to SQL view, copy Rookie's code into the SQL window, and modify it with your table/column names.

[MSAccessRookie]

What georgewilkinson said is exactly what I would have except that he got here and said it before I could check for replies this morning.

Once you have the SQL entered, let us know if there are any other issues. Feel free to post the SQL code you are using so that we can check it out for you

[Mithander]

Thank you all for your help, but despite the help, I still can't get this darn thing to work! So I copied and pasted the script as posted previously and edited to my needs. Everytime I run this query though, it doesn't finish and ends up giving me the message "Syntax error in JOIN operation." I have no idea what this means and how to fix it...
Below is the script I'm using, as you can see, I omited the "INNER JOIN PartTable6 ON {whatever they join on})" sections of the script cause I have no idea what it does...

Select PartName, TotalNeededperBoardperOrder
FROM (PET1-RevB INNER JOIN VolutionPartsLibrary ON PartName)
UNION
Select PartName, TotalNeededperBoardperOrder
FROM (SAMByteBus-RevB INNER JOIN VolutionPartsLibrary ON PartName)
UNION
Select PartName, TotalNeededperBoardperOrder
FROM (TWIN1-RevB INNER JOIN VolutionPartsLibrary ON PartName)
UNION
Select PartName, TotalNeededperBoardperOrder
FROM (TWIN2-RevB INNER JOIN VolutionPartsLibrary ON PartName)
Help please!
Some more information:
Tables:PET1-RevB
SAMbyteBus-RevB
TWIN1-RevB
TWIN2-RevB
VolutionPartsLibrary
Columns:PartName
TotalNeededperBoardperOrder

[dallr]

I would just say that you show us your listing of tables and state what results you want to have returned. In that way someone can write a query and explain how it is working.

dallr

[georgedwilkinson]

Hi Mithander,

Can you please simplify the query (remove all unions) and test each sub-union query? This will enable to you view each query in the visual editor and it will tell you if there is a problem. When all 4 of the queries work correctly, then you can union them together.

Does that make sense? It'll also help us to help you troubleshoot the problem.

[MSAccessRookie]

Originally Posted by Mithander

Thank you all for your help, but despite the help, I still can't get this darn thing to work! So I copied and pasted the script as posted previously and edited to my needs. Everytime I run this query though, it doesn't finish and ends up giving me the message "Syntax error in JOIN operation." I have no idea what this means and how to fix it...
Below is the script I'm using, as you can see, I omited the "INNER JOIN PartTable6 ON {whatever they join on})" sections of the script cause I have no idea what it does...

Select PartName, TotalNeededperBoardperOrder
FROM (PET1-RevB INNER JOIN VolutionPartsLibrary ON PartName)
UNION
Select PartName, TotalNeededperBoardperOrder
FROM (SAMByteBus-RevB INNER JOIN VolutionPartsLibrary ON PartName)
UNION
Select PartName, TotalNeededperBoardperOrder
FROM (TWIN1-RevB INNER JOIN VolutionPartsLibrary ON PartName)
UNION
Select PartName, TotalNeededperBoardperOrder
FROM (TWIN2-RevB INNER JOIN VolutionPartsLibrary ON PartName)
Help please!
Some more information:
Tables:PET1-RevB
SAMbyteBus-RevB
TWIN1-RevB
TWIN2-RevB
VolutionPartsLibrary
Columns:PartName
TotalNeededperBoardperOrder

Here we have the names of the tables that you are using, but try to give you a better answer, I need two more things:

1. The structure of each table involved (Column Names and Types)
2. Any Known expected relationships between the tables