Newbie help with query

[Holstein]

I imported data from a .txt file into an Access XP table.
The .txt file had information for 5 different record
types so I created 5 different tables and pasted the data
for each record type into each table so I could look at
the data and make sense of it. (the five different
records had different columns for info)> Now I have 5
tables with info in them named RecordType1, RecordType2
and so on. I am trying to create a query to look at all
the data in all the tables and whenever I add columns for
more than one table I don't get any data. The SQL query
is shown here: SELECT RecordType1.ID,
RecordType1.Record_Type, RecordType2.Case_Number,
RecordType3.Species_Code, RecordType4.Animal_ID
FROM (((RecordType1 INNER JOIN RecordType2 ON
RecordType1.ID = RecordType2.ID) INNER JOIN RecordType3
ON (RecordType1.ID = RecordType3.ID) AND (RecordType2.ID
= RecordType3.ID)) INNER JOIN RecordType4 ON
(RecordType1.ID = RecordType4.ID) AND (RecordType3.ID =
RecordType4.ID)) INNER JOIN RecordType99 ON
(RecordType1.ID = RecordType99.ID) AND (RecordType4.ID =
RecordType99.ID);

Sorry for the long post but I hope I explained what I'm
trying to do. Any help is appreciated. Thanks in advance.

 

[Les]

Are the values in ID the same for all your tables? It
looks like your query is joining all 5 of your tables on
ID. So, if there is a record in 4 of your tables, but not
all 5, you won't see that data.
I'm guessing you have some sort of Animal application
going. Do you know if there is a record type for every
animal? If there isn't, you won't get any data for that
animal.

 

[Holstein]

Thanks for the reply Les. Yes this database is going to
be used to keep track of all lab results on animals. I
removed the relationship for ID on the tables to see what
would happen and ran a query for all fields in all tables
and it pulled multiple entries for all records (Over one
million records). Can you tell me how to get unique
records only (one instance for each unique record)?
Thanks again.

 

[456]

Hi there,

what do you mean by 5 different record types? I could be wrong, but it looks
as though it's just a standand comma or tab delimited text file with the
first row representing column headings. If that's the case, just put the
whole thing into 1 table. Then use a query to cull the data you want to
view.

Curious,
Robert Dale

 

[Holstein]

Thanks for the reply Robert. You are right the data is
from a comma delimited field. I'm struggling with the
fact that record type 1 has different columns than record
type2 and so on. Can you elaborate on your solution. Like
I posted before, I'm a newbie. Thanks again.

 

[456]

Could you post a few lines of sample data from the text file and include the
headings? I'm not clear about your meaning of 5 different record types.

Robert Dale

 

[Holstein]

Here is a few lines from the text file.
The "03","04","01" at the beginning of the lines are the
different record types. The .txt file does not have any
headings for the data I'm trying to figure them out as I
go. I had to remove some of the data due to privacy
issues but I hope this helps.
"01","D03-049471","2003-10-14-
10.08.21.601208","XXX","removed data, removed
data","removed data","removed data","removed
data","XXXX","XXXX","","REMOVED
DATA","","REMOVED","XX","","","POR","PORCINE","MSPR","MISC
","US","xx","NBLS"
"03","D03-
049471","POR","PORCINE","MSPR","MISC",1649072,5,"Y","1-
4","","","","","","",0,"","",0,"","","","",0,"","",0,"",""
"04","D03-049471",1649072,4029540,"M.SIV.SQ","Influenza
sequencing","TSHM","Tissue Homogenate","2004-01-21-
08.05.50.883192","2004-01-21-
08.05.50.883192",3902779,0,.0000,"",.00

 

[Les]

Start with the table that has all the animals in it.
If you are tracking all lab results, they must apply to an
animal.
Next, bring in another table. Join them by ID. Then,
place your cursor on the join line and press your right
mouse button. You should get a choice of join
properties. Select everything from the animal table, and
only those from the other table that match. This way,
you'll see all animals and whatever is in the other table
that applied to any animal. You can do the same thing
with your other tables.
You may want a few queries, depending on the
information kept in the other table. Hope this gives you
a good starting point.

 

[Les]

I think you are correct in creating different tables for
record types. Where are you getting this txt file from?
Can they supply you with file layouts to help you with
your work?

Record type 01 looks like your main table. It seems to
have the identifying information.

The results that you're after will determine your query
structures. For instance, if you wanted all animals who
had influenza sequencing done, you would build a query
with table 4, selecting where column = influenza
sequencing, and left join it to table 1. This would give
you every row in table 4 that had influenza sequencing,
and the animal identification info from table 1.

Is this helping at all?

 

[Holstein]

Thanks very much for all the help. I think I've got it
now. I really appreciate your help Les and Robert. I
contacted the source of the data file and they sent me
the file layout. Once again, Thank you very much