Newbee here, Need some help with union query...

[jv1]

Database is for part numbers and usage. One table consists of part numbers on hand (MFG), and other specifics. The other table(s) consists of quantities of each part number (Quantity) that is exported weekly from SAP. Trying to blend 2 tables (only 2 fields per table) to give me weekly usage visable across the entire inventory, even if the value is 0 or null. There are both MFG and Quantity fields in both tables, and the MFG field in quantity table is duplicated for different dates of usage.?

This is what I have so far...it does not work.

SELECT [Parts Mar 12].Quantity, [Parts 2-24-13].Quantity
FROM [Parts 2-24-13] INNER JOIN [Parts Mar 12] ON [Parts 2-24-13].MFG = [Parts Mar 12].MFG
WHERE ((([Parts 2-24-13].Quantity)=[Parts Mar 12].[Quantity]))(+);

Any help would be appreciated, Thank you.

[alansidman]

Your statement ....it does not work is not much help. What happens? What are the results and what were you expecting? What does the (+) at the end of the SQL statement represent? Why is it there? In simple english,tell us what you want to happen within your query being as specific as possible.

[jv1]

Just gives me 2 blank fields with MFG and Quantity. The (+) was supposed to add the nulls to the end result, representing 0 usage.

What I am looking for is 2 fields, one with part numbers (MFG) and one with Quantity. The master table has a full list of part numbers, and the other table has the quantities and nulls. I need to see what I am using from my master table on a weekly basis.

[alansidman]

SELECT [Parts Mar 12].Quantity, [Parts 2-24-13].Quantity
FROM [Parts 2-24-13] INNER JOIN [Parts Mar 12] ON [Parts 2-24-13].MFG = [Parts Mar 12].MFG
WHERE ([Parts 2-24-13].Quantity)>0 or ([Parts 2-24-13].Quantity) is Null;

This should tell you what parts from the table 2-24-13 are greater than 0 or Null. I hope this is what you are looking for. If not, post back with more information.

[Cap Heresy]

Do you really have a different table for each date?

[jv1]

Do you really have a different table for each date?

Nope, the imported table from SAP has duplicates in MFG (Part Number) field based on dates they were ordered. I may have had an order for 2 lots of the same part number in the same week, but I only want to see the total quantity for each MFG in the final query. Not worried about dates of order. I am also looking for nulls in the final query, as I may not order any of certain MFG in a week. This whole database is for a point of use parts area, so the nulls are important to possibly phase out parts if needed.

[jv1]

SELECT [Parts Mar 12].Quantity, [Parts 2-24-13].Quantity
FROM [Parts 2-24-13] INNER JOIN [Parts Mar 12] ON [Parts 2-24-13].MFG = [Parts Mar 12].MFG
WHERE ([Parts 2-24-13].Quantity)>0 or ([Parts 2-24-13].Quantity) is Null;

This should tell you what parts from the table 2-24-13 are greater than 0 or Null. I hope this is what you are looking for. If not, post back with more information.

Thank you, very exciting to get results! Changed it a bit;
SELECT [Parts Mar 12].Quantity, [Parts 2-24-13].MFG
FROM [Parts 2-24-13] INNER JOIN [Parts Mar 12] ON [Parts 2-24-13].MFG = [Parts Mar 12].MFG
WHERE ([Parts Mar 12].Quantity)>0 or ([Parts 2-24-13].Quantity) is Null;

...and I got quantities in table 2 to populate with the associated MFG in table 1. Very cool! Thank you.


How would I get the duplicates to combine quantities? Example below: 5D0391-1 shows 2 quantities for 2 different days. Need to have 1 MFG with combined Quantities. I also need to show table 1 MFG's that where not present in table 2 as "0".

Quantity	MFG
3	861267-403A
1	SW43793
3	5D0391-1
8	5D0391-1
2	126944-01AD
7	862649-001B
3	862649-001B
9	SW43793
1	SW43793
1	SW43793
100	8GH007429-28
25	502745-190-9004
1	D2527715035400
2	D9249116000000
13	D9249116000000
1	F1000630-05
14	088656-037
15	1001815-139AAG
15	126962-01AD
2	D2527715037300
1	D2527715037300
2	D2527715037300
2	D2527715037300
2	D2527715037300
1	D2527715037300
100	SEAL/PLASTIC
3	11-0787-1060
1	11-0787-1060
1	11-0787-1060
24	TY652073
10	849900-417

[jv1]

"I also need to show table 1 MFG's that where not present in table 2 as "0"."

Clarification; I also need the query to show a quantity of "0" for MFG's in table 1 [Parts 2-24-13] that were not MFG's present in table 2 [Parts Mar 12].

[alansidman]

To get the sums of the parts you will need to create an aggregate query. Use your current query as the data source for the aggregate query. Here is a link on how to do the aggregate query.

http://www.techonthenet.com/access/f...umeric/sum.php

For the zero quantities, use the query wizard and do an unmatched query.

[jv1]

Works great, thank you very much!