to create a Query with 2 Tables

  • Thread starter Thread starter Markus Mortsiefer
  • Start date Start date
M

Markus Mortsiefer

Hallo

I hope some one can help me im am trying to do the following, I have 2
tables a Main table and a Purge table where i have old data stored that was
in the Main table. Both tables have the same setup. So what i would like to
do is have a query that will show me the date from both tables again the
only way i can do it know is by creating a new table where i insert both
tables together again is there a way i can do that with out creating a new
table when i want to see both tables data together.

Best regards
Markus Mortsiefer
 
If you columns are in the same order for both tables:

SELECT * FROM [Main Table]

UNION

SELECT * FROM [Purge Table]
 
MDW said:
If you columns are in the same order for both tables:

SELECT * FROM [Main Table]

UNION

SELECT * FROM [Purge Table]
Click to expand...

PMFBI

If some record erroneously occurs in
both tables, you won't see that unless
you use

UNION ALL

(plus it is faster).

And I might want to know where
each record comes from

SELECT "Main" AS WhichTable, * FROM [Main Table]
UNION ALL
SELECT "Purge", * FROM [Purge Table]

Apologies again for butting in.

Gary Walter
 
Hallo

I want to say thank you its working well the only thing I have seen is that
I can now not change the data is there a way I can do this but it allows me
to still change the data even they are in two different tables but the
record will either be in the one or the other table never in both the at the
same time

Best regards

Markus

Gary Walter said:
MDW said:
If you columns are in the same order for both tables:

SELECT * FROM [Main Table]

UNION

SELECT * FROM [Purge Table]
Click to expand...

PMFBI

If some record erroneously occurs in
both tables, you won't see that unless
you use

UNION ALL

(plus it is faster).

And I might want to know where
each record comes from

SELECT "Main" AS WhichTable, * FROM [Main Table]
UNION ALL
SELECT "Purge", * FROM [Purge Table]

Apologies again for butting in.

Gary Walter
Click to expand...
 
Hi Markus,

Sorry, union queries are not updateable.

Have you thought about setting up
a form bound to Main table and a
subform bound to Purge table?

Possibly each table relates to each
other in some master/child relationship;
or you set up no master/child relationship,
but on the Purge subform you have some
search combo that would show relevant
Purge data based on what record was
currently being shown in the Main form.

A general rule of thumb is you edit data
in a form, not a query view anyway. Queries
provide sources for forms or reports. If possible,
I suggest using the power of form/subform to
get the job done.

Good luck,

Gary Walter


Markus Mortsiefer said:
Hallo

I want to say thank you its working well the only thing I have seen is that
I can now not change the data is there a way I can do this but it allows me
to still change the data even they are in two different tables but the
record will either be in the one or the other table never in both the at the
same time

Best regards

Markus

Gary Walter said:
MDW said:
If you columns are in the same order for both tables:

SELECT * FROM [Main Table]

UNION

SELECT * FROM [Purge Table]
Click to expand...

PMFBI

If some record erroneously occurs in
both tables, you won't see that unless
you use

UNION ALL

(plus it is faster).

And I might want to know where
each record comes from

SELECT "Main" AS WhichTable, * FROM [Main Table]
UNION ALL
SELECT "Purge", * FROM [Purge Table]

Apologies again for butting in.

Gary Walter
Click to expand...
Click to expand...
 
Back
Top