Macro Help with Combing Table with Date Query

[Jerseynjphillypa]

I have two sets of tables I want to create from App Query and App Query 2.



But i have a Query called dateQuery which will ask me to enter a date and with this date I enter into the Query, I want the date to be added to the to tables I created from the App Query and App Query 2. So when I run this Macro again with updated information in the table, the data will append to the table with a new date entered into the dateQuery.

After the date is enter into the Query and the two table have the date in, I want the two tables that are created from App Query and App Query 2 to combined together into a table called AlldataTogether.

[June7]

If I understand: The RawData tables are data imported from somewhere and you want to add these records to a master table called AllDataTogether and this will be a recurring process. You also need a date value included with each record.

1. First create the AllDataTogether table

2. Don't see need for the date MakeTable query

3. Instead of running two append queries and making tables, build two select queries and union them and use that union as the basis for an append. There is no designer or wizard for UNION query. Must type into the SQL View window of query designer. Here is one SQL statement combining all of this (also must type into the SQL View window):
INSERT INTO AllDataTogether ( DateEnter )
SELECT RawInfoUNION.*, [enter date] AS DateEnter
FROM (SELECT Information.Item, Location, Supplier, Used, Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item
UNION SELECT [Information 2].Item, Location, Supplier, Used, Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item) As RawInfoUNION;

Caution, using query input parameter prompts is risky. Cannot validate the input. If date is mistyped query will still try to run and if the entry is not valid date, the query will error.

[Jerseynjphillypa]

Thanks for the help..

I get the following error message when I run the SQL
The INSERT INTO statement contains the following unknown field name: 'Item". Make sure you have typed the name correctly, and try the operation again.


Sorry but I was unclear with my first post

I would like the two independent queries to have the DateEnter and then after the two table have the date entered in them, I would like the table to combine together in AlldataTogether.

[June7]

I tested the SQL with the sample data you provided and it works. Post the SQL statement you used. Should have been able to just copy/paste what I suggested unless your actual db structure is different from the example provided.

The same date will be entered for all records. Do you want different dates for each RAW set?

[Jerseynjphillypa]

Yes I would want the date to be the same in both queries.

Also once the date is entered into both queries, then I want the tables to combine.

Hence I taught I needed a Macro to do this, since I want both queries to append the new data with the date I will enter in 'Date Enter' and then it will union both queries table together in AllDataTogether


I also provided you with the db with me testing out the union under the query datetest query.

I think I might be creating the AllDataTogether. I would like the table to appear just like when I click on the view button. But when I click on the run button, I get a error.

[June7]

The append query I suggest won't work because the AllDataTogether table doesn't have fields (except for the AutoNumber). Need to finishing building then run the append.

[Jerseynjphillypa]

Thanks for the help..

Also how would I get the DateEnter to enter the dates in the App Query and App Query 2 tables called Table and Table 2. Since I also want the same date I enter to appear in those two tables as well.

Since in the App Query and App Query 2 sometimes I would want to Filter by Location and only append some data into the table.

so if I set a criteria under Location to NJ, I only want the data to show in Table and only NJ Location to Appear in the All DataTogether Table with the date I enter in DateEnter.

Also if you could explain where SELECT RAWInfo came from

[June7]

The query I suggest has an input parameter prompt for the date. It will apply to all records. Can apply filter criteria to the nested SELECT queries or to the outer SELECT query. RawInfoUNION is query alias name used in the SQL for the nested UNION query.

[Jerseynjphillypa]

Thanks for the help

INSERT INTO AllDataTogether ( DateEnter )
SELECT RawInfoUNION.*, [enter date] AS DateEnter
FROM (SELECT Information.Item, Location, Supplier, Used, Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item
UNION SELECT [Information 2].Item, Location, Supplier, Used, Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item) As RawInfoUNION
HAVING((([Information].Location)="NJ") AND (([Information 2].Location)="CA"));

I get a error Having clause ([Information].Location)="NJ") AND (([Information 2].Location)="CA") without grouping or aggregation error.

[June7]

Can't use AND operator, no record will match the condition of Location = "NJ" and "CA", use OR.

INSERT INTO AllDataTogether ( DateEnter )
SELECT RawInfoUNION.*, [enter date] AS DateEnter
FROM (SELECT Information.Item, Location, Supplier, Used, Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item
UNION SELECT [Information 2].Item, Location, Supplier, Used, Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item) AS RawInfoUNION
WHERE (((RawInfoUNION.Location)="NJ")) OR (((RawInfoUNION.Location)="CA"));

HAVING is for Totals (aggregate) queries.

[Jerseynjphillypa]

Thanks for the help.

If I want to Union 10 tables into one table (AlldataTogether). Could you show me one example with SQL if I had another 2 more tables called Information 3 and Raw Data 3.

[June7]

Open query in SQL View and copy/paste the UNION SELECT line inside the parenthesis and change the 2's to 3's. Up to 50 lines allowed in a UNION.

[Jerseynjphillypa]

Thanks for all the help

[Jerseynjphillypa]

The append query I suggest won't work because the AllDataTogether table doesn't have fields (except for the AutoNumber). Need to finishing building then run the append.

How would I remove the AutoNumber from the table

[June7]

Open table in design view and delete the field. However, leaving it should not be an issue.