Union Query gets evrything I don't want

[m198redleg]

This is First time posting, I have gleaned a lot from listening. This has got me by the hair. Query Code is as follows, I find alot of request for coding;

TABLE "01DATA" WEEK, D, A, DTE_ENL, ([LAST_NAME]&" "&[FIRST]&" "&[MI])AS F_NAME, SSN, QT, DEPT, TYPE_ENL, SECTION, REC_MIT, CLEAR, DTE_IN

QUERY "04_DATAud" WEEK, D, A, DTE_ENL, ([LAST_NAME]&" "&[FIRST]&" "&[MI])AS F_NAME, SSN, QT, DEPT, TYPE_ENL, SECTION, REC_MIT, CLEAR, DTE_IN
Code is
SELECT *
FROM [01DATA]

UNION

SELECT *
FROM [04_DATAud]

INSERT INTO tblJacobs
SELECT *
WHERE DTE_IN = " "
ORDER BY WEEK;

It will not Make a new Table => tblJacobs
I COPY and pasted structure only tblJacobs and utilized the APPEND and UPDATE nichts.
I am havinbg to bring up their historical files and merge em into one, sort and print out based on five or six dif queries they will be able to see their progress, either up or down.
To most of you this is a trivial item, but my mind at the middle of the day is mush.
Thanks for any help you can afford me. They are utilizing Access 97.

 

[hneal98]

You can have it just dump the data into tblJacobs or you can have it create the table like this:

SELECT *
into tblJacobs
FROM
[01DATA]
WHERE DTE_IN = " "

UNION

SELECT *
FROM [04_DATAud]
WHERE DTE_IN = " "
ORDER BY WEEK;

The above creates the table.

 

[m198redleg]

Thanks hneal98.

I tried different appraches but came up with the same thing. "An Action Query can not be used as a Row Source."
I do not understand how placing the INTO tblJACOBS makes it go into row sourcing, unless it is having to drop row up on row.

 

[dhookom]

Create a stand alone union query and use it as the source for the INSERT query. You could also try:


INSERT INTO tblJacobs
SELECT *
FROM [01DATA]
UNION
SELECT *
FROM [04_DATAud]
WHERE DTE_IN = " "
ORDER BY WEEK;

I would expect that DTE_IN might be a Date/Time field and hence would never equal " ".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[m198redleg]

Thank you folks, dhookom and hneal98
This Query works great now. Thanks

 

[hneal98]

Good job dHookom, I did not think of that.