Select Query for two tables to treat fields the same

[Thorson]

I currently have two tables:
#1: tblBirthInformation
#2: tblPurchase/Donations

As animals die or are sold or transferred they recieve a disposition number
which is entered into another table. I then have a query set up that
displays the animal's birth date, current location, breed code etc. Each
indivdual animal ID is pulled from the tblBirthInformation table. I would
like it to pull in additional animals from the tblPurchase/Donations table
and treat the fields exactly the same as it treats the tblBrithInformation
table. They will have some of the same fields with different records, (also
many different fields that will not be used in the query).

Is this possible? How would I set up the relationships in the query?

My other option would be to copy partial records from the
tblPurchase/Donation table after update to the tblBirthInformation table, but
this would create duplicate information on the same animal.

 

[Lord Kelvan]

i think you want a union query but i may be wrong

try

select field1,field2,field3
from tblBirthInformation
union all
select field1,field2,field3
from tblPurchase/Donations

where field1 field2 field3 are the same field in each table

you can add more fields as many as you want

if this isnt what you want please define it more clearly give examples of
data in the tables and how you want it to appear after

Regards
Kelvan

 

[NetworkTrade]

and, in addition to LK's advice; if this is early in your db creation, you
might really consider merging those tables....

whether purchased or birthed or donated... doesn't necessarily mean they
should be in different tables...these are just optional values.... I would
think you would want one master table of all....

a horse is a horse of course of course and no one can talk to a horse of
course...

 

[Thorson]

The only problem with that is that purchased and donated animals have
different fields than the born animals, only a few are the same, it would
create many blank fields for most records... which from what I understand is
something you should try to prevent...

Thanks

 

[Thorson]

That sounds like what I want... but I've never made a union query before,
I've also never made a query in SQL View before. This is what I have so far:

SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblBirthInformation
UNION ALL
SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblPurchase/Donations

When I try to run the query it says "Syntax error in FROM Clause" I'm
assuming I typed something in wrong... any suggestions?
thank you!

 

[John Spencer]

SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblBirthInformation
UNION ALL
SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM [tblPurchase/Donations]

IT is a really good idea to never name tables or fields with any
characters other than letters, numbers, and the underscore character.
If don't do so any name that contains a character other than those will
need to be surrounded with square brackets []. Also you should avoid
using reserved words such as Date, Time, Field, Select, FROM, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

That sounds like what I want... but I've never made a union query before,
I've also never made a query in SQL View before. This is what I have so far:

SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblBirthInformation
UNION ALL
SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblPurchase/Donations

When I try to run the query it says "Syntax error in FROM Clause" I'm
assuming I typed something in wrong... any suggestions?
thank you!

i think you want a union query but i may be wrong

try

select field1,field2,field3
from tblBirthInformation
union all
select field1,field2,field3
from tblPurchase/Donations

where field1 field2 field3 are the same field in each table

you can add more fields as many as you want

if this isnt what you want please define it more clearly give examples of
data in the tables and how you want it to appear after

Regards
Kelvan

 

[Thorson]

Thank you! Works Great.

SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblBirthInformation
UNION ALL
SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM [tblPurchase/Donations]

IT is a really good idea to never name tables or fields with any
characters other than letters, numbers, and the underscore character.
If don't do so any name that contains a character other than those will
need to be surrounded with square brackets []. Also you should avoid
using reserved words such as Date, Time, Field, Select, FROM, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

That sounds like what I want... but I've never made a union query before,
I've also never made a query in SQL View before. This is what I have so far:

SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblBirthInformation
UNION ALL
SELECT [eartag],[calftattoo],[calfbirthdate],[calfsex]
FROM tblPurchase/Donations

When I try to run the query it says "Syntax error in FROM Clause" I'm
assuming I typed something in wrong... any suggestions?
thank you!

i think you want a union query but i may be wrong

try

select field1,field2,field3
from tblBirthInformation
union all
select field1,field2,field3
from tblPurchase/Donations

where field1 field2 field3 are the same field in each table

you can add more fields as many as you want

if this isnt what you want please define it more clearly give examples of
data in the tables and how you want it to appear after

Regards
Kelvan