Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-25-2008, 02:13 PM   #1
Mithander
Registered User
 
Join Date: Jun 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Mithander is on a distinguished road
Question A Very Odd Querying Problem

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 by Mithander; 06-25-2008 at 02:29 PM. Reason: Additional Information
Mithander is offline   Reply With Quote
Old 06-25-2008, 02:52 PM   #2
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
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
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Old 06-25-2008, 03:13 PM   #3
Mithander
Registered User
 
Join Date: Jun 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Mithander is on a distinguished road
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.

Mithander is offline   Reply With Quote
Old 06-25-2008, 03:28 PM   #4
georgedwilkinson
AWF VIP
 
Join Date: Mar 2008
Location: Houston, TX USA!!!
Posts: 3,856
Thanks: 0
Thanked 9 Times in 9 Posts
georgedwilkinson has a spectacular aura about georgedwilkinson has a spectacular aura about
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.
__________________
Thanks,
George Wilkinson
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
georgedwilkinson is offline   Reply With Quote
Old 06-26-2008, 06:14 AM   #5
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
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
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Old 07-04-2008, 09:08 AM   #6
Mithander
Registered User
 
Join Date: Jun 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Mithander is on a distinguished road
Errors arn't fun...

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
Mithander is offline   Reply With Quote
Old 07-04-2008, 01:31 PM   #7
dallr
AWF VIP
 
dallr's Avatar
 
Join Date: Feb 2008
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
dallr is on a distinguished road
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

dallr is offline   Reply With Quote
Old 07-05-2008, 04:38 PM   #8
georgedwilkinson
AWF VIP
 
Join Date: Mar 2008
Location: Houston, TX USA!!!
Posts: 3,856
Thanks: 0
Thanked 9 Times in 9 Posts
georgedwilkinson has a spectacular aura about georgedwilkinson has a spectacular aura about
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.
__________________
Thanks,
George Wilkinson
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
georgedwilkinson is offline   Reply With Quote
Old 07-07-2008, 05:29 AM   #9
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Quote:
Originally Posted by Mithander View Post
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

__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Reply

Tags
duplicates , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Odd multi-form with cascading combo box requery problem JP-C Forms 10 01-30-2007 09:00 AM
Odd front end back end security problem Keith Nichols General 0 07-28-2006 01:34 AM
ItemData combo box problem (Access 2000) ilkglass Modules & VBA 1 07-08-2006 06:13 AM
Odd page up \ down problem David b Modules & VBA 2 09-05-2004 07:48 AM
Querying with Date Format Problem (Again) Ally Queries 6 06-19-2002 01:44 AM




All times are GMT -8. The time now is 03:24 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World