Appending the Null Values to One table

[Jerseynjphillypa]

Currently my SQL below is finding all Sell Item that are “Y”, and retrieving the values from the tables and appending the information to one table called AlldataTogether. Which works fine thanks to all the user who helped me out on this forum.

This is where I need your help again
If Sell is already “Y” in the tables (Sell Information NJ or Sell Information CA), I only want the Null Value by Item from CHINA Import Information to be filter by the field Location “CSA”. For this example Toy Car is already “Y” so I would need Acces. And LED Light to append to the AllDataTogether
But this is the tricky part I also don’t understand how to do
All the tables are related by Item, I need to retrieve the following information and append to AlldataTogether for the Null Values from CHINA Import Information as follow


Item: Null value by Item from CHINA Important Information based on if Sell is Y
Location: Location pulled from CHINA Important Information by Item
Supplier: supplier pulled from CHINA Important Information by Item
Sell: If Value is Null from the CHINA Important, I want the text string to say “CHINA”
Account Manger: Account Manger pulled from CHINA Important Information by Item
Stock: The value of stock can be either in Sell Data CA or Sell Data NJ
Total: The value of Total can be either in Sell Data CA or Sell Data NJ
Code: Code pulled from either Sell Information NJ or Sell Information CA by item.

INSERT INTO AllDataTogether ( DateEnter )SELECT RawInfoUNION.*, [Enter Date] AS DateEnter
FROM (SELECT [Sell Information NJ].[Item],Location, [Supplier], [Sell], [Account Manger],[Stock],[Total],Code FROM [Sell Information NJ] LEFT JOIN [Sell Data NJ] ON [Sell Information NJ].[Item] = [Sell Data NJ].[Item]
Union Select [Sell Information CA].[Item], Location, [Supplier], [Sell], [Account Manger],[Stock],[Total],Code FROM [Sell Information CA] LEFT JOIN [Sell Data CA] ON [Sell Information CA].[Item] = [Sell Data CA].[Item])  AS RawInfoUNION
WHERE ((RawInfoUnion.[Sell]="Y"));

[June7]

Not sure I fully understand your criteria but maybe this will get you down the right path.

This query will retrieve the Acces. and LED Light records from CHINA with the Stock, Total, Code fields (note the use of UNION query again).

SELECT [CHINA Import Information].Item, [CHINA Import Information].Location, [CHINA Import Information].Supplier, [CHINA Import Information].[Account Manger], Stock, Total, Code
FROM CA_NJ_UNION INNER JOIN [CHINA Import Information] ON CA_NJ_UNION.Item = [CHINA Import Information].Item
WHERE ((([CHINA Import Information].Location)="CSA") AND ((CA_NJ_UNION.Sell) Is Null));

Now the INSERT SELECT:
INSERT INTO AllDataTogether (Item, Location, DateEnter, Supplier, Sell, [Account Manger], Stock, Total, Code)
SELECT Item, Location, [Enter Date] AS DateEnter, Supplier, "CHINA" AS Sell, [Account Manger], Stock, Total, Code
FROM CHINA_Expanded;

[Jerseynjphillypa]

Thanks for the helping me out June7.

How would run the Query to test it out, when I run the first SQL code, I get the following error, "The Microsoft Access database engine cannot find the input table or query 'CA_NJ_Union'. Make sure it exists that its name is spelled correctly.


Also for the second SQL code, would I need to create a CHINA_Expanded table?

Also I don't understand how the DateEnter is being pulled in

[June7]

That's because you need to create and save the UNION query I called CA_NJ_Union. Or include it as a subquery in the SQL as you did in the one in your first post.

The first query in my post is CHINA_expanded. That was implied by showing these queries in sequence.

All of these could be nested as one very long SQL statement if you want to tackle it.

As in the query in your first post, [Enter Date] is an input parameter prompt.

[Jerseynjphillypa]

I will try to tackle the nested SQL a little later, but I'm still getting a error when I save the Union Query called as Test Union or CA_NJ_Union

I get the error circular reference caused by 'Test Union' or

"The Microsoft Access database engine cannot find the input table or query 'CA_NJ_Union'. Make sure it exists that its name is spelled correctly.

[June7]

CA_NJ_Union is the same union query used in your query in the first post:
SELECT [Sell Information NJ].[Item],Location, [Supplier], [Sell], [Account Manger],[Stock],[Total],Code FROM [Sell Information NJ] LEFT JOIN [Sell Data NJ] ON [Sell Information NJ].[Item] = [Sell Data NJ].[Item]
UNION SELECT [Sell Information CA].[Item], Location, [Supplier], [Sell], [Account Manger],[Stock],[Total],Code FROM [Sell Information CA] LEFT JOIN [Sell Data CA] ON [Sell Information CA].[Item] = [Sell Data CA].[Item]

CHINA_expanded (as shown in my earlier post):
SELECT [CHINA Import Information].Item, [CHINA Import Information].Location, [CHINA Import Information].Supplier, [CHINA Import Information].[Account Manger], Stock, Total, Code
FROM CA_NJ_UNION INNER JOIN [CHINA Import Information] ON CA_NJ_UNION.Item = [CHINA Import Information].Item
WHERE ((([CHINA Import Information].Location)="CSA") AND ((CA_NJ_UNION.Sell) Is Null));

Then the last query (as shown in my earlier post):
INSERT INTO AllDataTogether (Item, Location, DateEnter, Supplier, Sell, [Account Manger], Stock, Total, Code)
SELECT Item, Location, [Enter Date] AS DateEnter, Supplier, "CHINA" AS Sell, [Account Manger], Stock, Total, Code
FROM CHINA_Expanded;