Query from four tables that duplicates records

[tothemax]

I have a database set up with a Report table that three other tables are related (many to many relationships) to the same primary key of the first table. i.e. ReportID primary in first table and foreign in other three tables. The Report table is also related to an Employee Table. The three other tables have unique info that I need in different tables. All info is entered thru a main form based on the first table and subforms based on the other three tables. The data entry works fine. The correct info goes into each table. However, when I try to create a query based on the Report table and more than one of the other tables, I get a multiplying effect. i.e. if I have four records in the task hours subform, it duplicates the Report form vacation hours record four times for each record in it instead of adding the two together. Therefore, I get multiple records repeated from the tables giving false totals because the same records are counted several times.

 

[braindead2]

I can only assume that since you are not expecting to return 4 records in a 1 to many relationship that there is either something wrong with your joins in the query or perhaps you can use a select distinct clause.
Generally it is normal behavior for a query to return a record for each one that exists, then you limit what is returned with a where clause.

 

[tothemax]

The query I need and the tables are as follows:
Empl - Report - Shoe - Color - Size
Name - Date - ShoeType - ColorType - Size

My query needs to pull for each Employee a date multiple shoetypes, shoecolors, shoesizes. (This is just an illustration. The way my employer want the database set up requires multiple tables.) What the query produces is multiple same dates for each shoetype times the colortype, etc.

I am new to SQL. How would I direct the query to give the following:
Empl1 - 1/1/2002- Shoe1 - ShoeBlack - ShoeSize7
Empl1 - 1/1/2002 - Shoe2 - ShoeRed - ShoeSize5

instead of something like:
Empl1 - 1/1/2002 - Shoe1 - ShoeBlack - ShoeSize7
Empl1 - 1/1/2002 - Shoe1 - ShoeBlack - ShoeSize5
Empl1 - 1/1/2002 - Shoe2 - ShoeBlack - ShoeSize7
Empl1 - 1/1/2002 - Shoe2 - ShoeBlack - ShoeSize5
Empl1 - 1/1/2002 - Shoe1 - ShoeRed - ShoeSize7
Empl1 - 1/1/2002 - Shoe1 - ShoeRed - ShoeSize5
Empl1 - 1/1/2002 - Shoe2 - ShoeRed- ShoeSize7
Empl1 - 1/1/2002 - Shoe2 - ShoeRed - ShoeSize5
etc.

 

[GoDawgs]

Well if your tables are Many to Many relationships (like you said at the top) then your query is always gonna look the wrong way. You need to have 1 to Many relationships set up...I'm not exactly sure what's going on with your database, but you my guess is you need some kind of intermediate table that is connected between report and the 3 other tables (shoe, color, and size)...all that would be in this table would be a primary key (autonumber if you want) and then the foreign keys from all 4 of the other tables (report, shoe, color, and size) and that would be all. Then in your query you would connect all the foreign keys to primary keys, then when you run the report it should work. Hope that helps.