A Very Odd Querying Problem (1 Viewer)

Mithander

New member
Local time
Today, 04:54
Joined
Jun 25, 2008
Messages
3
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.
 
Last edited:

MSAccessRookie

AWF VIP
Local time
Today, 07:54
Joined
May 2, 2008
Messages
3,428
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

New member
Local time
Today, 04:54
Joined
Jun 25, 2008
Messages
3
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.
 
Local time
Today, 06:54
Joined
Mar 4, 2008
Messages
3,856
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

AWF VIP
Local time
Today, 07:54
Joined
May 2, 2008
Messages
3,428
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

New member
Local time
Today, 04:54
Joined
Jun 25, 2008
Messages
3
Errors arn't fun...

Thank you all for your help, but despite the help, I still can't get this darn thing to work! :mad: 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! :confused:
Some more information:
Tables:pET1-RevB
SAMbyteBus-RevB
TWIN1-RevB
TWIN2-RevB
VolutionPartsLibrary
Columns:partName
TotalNeededperBoardperOrder
 

dallr

AWF VIP
Local time
Today, 04:54
Joined
Feb 20, 2008
Messages
81
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
 
Local time
Today, 06:54
Joined
Mar 4, 2008
Messages
3,856
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

AWF VIP
Local time
Today, 07:54
Joined
May 2, 2008
Messages
3,428
Thank you all for your help, but despite the help, I still can't get this darn thing to work! :mad: 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! :confused:
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
 

Users who are viewing this thread

Top Bottom