I think my relationship is wrong. Help

[Guest]

Bear with me on this lengthy explanation..

I've created a Warranty database that accepts a Warranty Claim #, Serial #,
a Category # and Category name from a combo box, along with a Warranty
amount. I chose to have a separate table hold the serial # and the Waranty
Amounts because it is possible to have the same serial # have multiple
warranty amounts for different categories of warranties.

My Issue: I created a report that I want sort by date and location to read
Category, Category Name, and Warranty Amount. When I run the report, I get
duplicates to the category where I input 2 different warranty amounts. For
instance...If I enter 2 different Warranty Amounts for the same serial
number, but for 2 different categories, I get a report that contains BOTH
categories with BOTH warranty amounts. EX. If I enter Serial #123 and
Category A with $10 and Serial #123 and Category B with $20, the report
returns the following:
A NameA $10
B NameB $10
A NameA $20
B NameB $20
Logically, I only want the following results:
A NameA $10
B NameB $20

Background info:
I believe the issue is how my query is set up. I have a maint table that
accepts the follwing input from a form:
Warranty Claim#
Serial #
Category
Cateogory Name
Then within a sub-form I can enter
Serial #
Warranty Date
Warranty $

My query relationship has 3 tables; The main table containing the following
data:

Warranty Claim#
Serial #
Category
Cateogory Name
A second table exists with the following data:
Serial #
Warranty Date
Warranty $
A third table performs a lookup for category and category name. The data is
a follows:
Category
Category Name

I have the following relationships:
Table1: Serial # to Table2: Serial #
Table1: category to Table3: category

The reasoning behind the relationships were to have the query feed a report
where the Table1 Serial# would match Table2 Serial# and add the Warranty Date
and Warranty Amount to the report. Simultaneously, Table1 Category would
match Table3 Category and return the Category Name.

My Join properties are one to one (Option 1). Please point me in the right
direction to resolve this issue.

Thanks for your patience and understanding.

 

[Tom Lake]

Background info:

I believe the issue is how my query is set up. I have a maint table that
accepts the follwing input from a form:
Warranty Claim#
Serial #
Category
Cateogory Name

I'd get rid of Category Name in this table and just use the looked up value.

Then within a sub-form I can enter
Serial #
Warranty Date
Warranty $

I have the following relationships:
Table1: Serial # to Table2: Serial #
Table1: category to Table3: category

The reasoning behind the relationships were to have the query feed a
report
where the Table1 Serial# would match Table2 Serial# and add the Warranty
Date
and Warranty Amount to the report. Simultaneously, Table1 Category would
match Table3 Category and return the Category Name.

My Join properties are one to one (Option 1). Please point me in the
right
direction to resolve this issue.

Serial # has to be the Primary Key (PK) in Table 1 and NOT the PK in Table 2
to have a One-to-Many relationship between the two. Is that the case?

Tom Lake

 

[Guest]

Thanks Tom. I needed to make the Warranty Claim# the PK and use the Many To
One relationship. This seems to have resolved my problem. Thank again, your
direction and advice have been invaluable. I'm somewhat embarrassed that I
didn't see that as a solution. Another learning experience.