Help with queries. HELP!

[Guest]

I'm trying to set up a query in my database bu having big trouble. My tables
are set up as follows
[MasterTable] feilds: serailno, otherfield1, otherfield2, otherfield3

[Table1] feilds; field1, serialno(lookup from table1), field2, field3

[Table2] feilds; field4, serialno(lookup from table1, field5, field6

[Table3] and so on... (about 10 tables)

What i'm trying to setup is a query that shows all records for each serial
no. Serialno's are being used in more than one table, but are never used in
all tables. Would it be some form of union query? I have no idea where to
start.

Any help would be much appreciated as this is vital to my database.

Thanks

 

[LeAnne]

I'm trying to set up a query in my database bu having big trouble. My tables
are set up as follows
[MasterTable] feilds: serailno, otherfield1, otherfield2, otherfield3

[Table1] feilds; field1, serialno(lookup from table1), field2, field3

[Table2] feilds; field4, serialno(lookup from table1, field5, field6

[Table3] and so on... (about 10 tables)

What i'm trying to setup is a query that shows all records for each serial
no. Serialno's are being used in more than one table, but are never used in
all tables. Would it be some form of union query? I have no idea where to
start.

Hi Enrico,

I'd suggest starting by looking at your db design. Your example of
sequentially numbered fields within tables, as well as multiple tables
with identical structure, suggests an unnormalized design. You need to
get rid of the groups of repeating information so that each field in a
table represents a unique type of information. There are many good
references that explain the process of normalization to organize groups
of fields into tables. Here are a couple:

http://support.microsoft.com/default.aspx?scid=kb;en-us;283698

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534

Hope this helps,

LeAnne

 

[Guest]

Sorry should have been clearer, those are not the title of my fields or
tables i just filled them out because the names were not relevant to what i
was trying to explain. The tables do not have identical designs and all hold
info unique to that table except for the serialno field.

I'm trying to set up a query in my database bu having big trouble. My tables
are set up as follows
[MasterTable] feilds: serailno, otherfield1, otherfield2, otherfield3

[Table1] feilds; field1, serialno(lookup from table1), field2, field3

[Table2] feilds; field4, serialno(lookup from table1, field5, field6

[Table3] and so on... (about 10 tables)

What i'm trying to setup is a query that shows all records for each serial
no. Serialno's are being used in more than one table, but are never used in
all tables. Would it be some form of union query? I have no idea where to
start.

Hi Enrico,

I'd suggest starting by looking at your db design. Your example of
sequentially numbered fields within tables, as well as multiple tables
with identical structure, suggests an unnormalized design. You need to
get rid of the groups of repeating information so that each field in a
table represents a unique type of information. There are many good
references that explain the process of normalization to organize groups
of fields into tables. Here are a couple:

http://support.microsoft.com/default.aspx?scid=kb;en-us;283698

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534

Hope this helps,

LeAnne

 

[Marshall Barton]

I'm trying to set up a query in my database bu having big trouble. My tables
are set up as follows
[MasterTable] feilds: serailno, otherfield1, otherfield2, otherfield3

[Table1] feilds; field1, serialno(lookup from table1), field2, field3

[Table2] feilds; field4, serialno(lookup from table1, field5, field6

[Table3] and so on... (about 10 tables)

What i'm trying to setup is a query that shows all records for each serial
no. Serialno's are being used in more than one table, but are never used in
all tables. Would it be some form of union query? I have no idea where to
start.

I'm not sure what you want the result to look like, but try
using outer joins to connect the various tables together:

SELECT [MasterTable].serailno, [MasterTable].otherfield1,
[MasterTable].otherfield2, [MasterTable].otherfield3,
[Table1].field1, [Table1].field2, [Table1].field3,
[Table2].field4, [Table2].field5, [Table2].field6,
[Table3].field7
FROM (([MasterTable] LEFT JOIN [Table1]
ON [MasterTable].serailno = [Table1].serailno)
LEFT JOIN [Table2]
ON [MasterTable].serailno = [Table2].serailno)
LEFT JOIN [Table3]
ON [MasterTable].serailno = [Table3].serailno

 

[LeAnne]

Sorry should have been clearer, those are not the title of my fields or
tables i just filled them out because the names were not relevant to what i
was trying to explain. The tables do not have identical designs and all hold
info unique to that table except for the serialno field.

Hi enrico,

I'm still suspecting a nonnormal design. When you say, "the tables...all
hold info unique to that table," does this mean that you have a
different table for each year, or each month of the year, or each sales
office, or something similar? If this is the case, you need to normalize
your tables to get the best use of your relational database. If I am
incorrect, then I guess I'm not understanding your problem. Could you
post a brief description of your tables, the relevent fields (real names
this time), and their relationships?

LeAnne