wierd duplicates problem

[Krzysztof via AccessMonster.com]

good day to all

i am having a weird duplicates problem with a query. i initally started in
SQL, but then went to the query designer to ease the tweaking process. i
have two tables who share a one-to-many relationship. in this query, i have
orders, order type, etc., and i have an expression that basically looks at
information in the table, and displays and text value based on criteria.

query fields:

Order# | order desc | order type | Expr1: iif(mid(({somefield}),10,4)=
{something},"Recall"," ") | Date

my problem is that {somefield} has different values in the one table:

order | {somefield}
1 1234
1 1235
1 1329
etc..

so, naturally, it will create duplicates in the query if your relationship
isn't correct.

as a one-to-one: obviously not correct
as a one-to-many: better, but still get duplicates like:

order | type | Expr1
1 REG Recall
1 REG {blank}

so, it is creating a blank record for the recall, and a record that actually
gives me what i want. otherwise, the records that do not follow the criteria
work fine.

i also have the "totals" on. if they are off, it gives bad results.

any help would be greatly appreciated.

VMTIA

~K

 

[Guest]

Is there a join line between the two tables?

Is one of the fields in the join in bold print (A.K.A. the primary key of
that table).

If you don't have a join, that will cause a cartisian join. If at least one
side doesn't have a primary key in the join, it's very possible to have
duplicate records in both tables.