Records Squared?

[Maker1]

I am trying to run an update query on Access 2003. I have two tables that link (one-to-one) on a common field (QD#). I ran the query to make sure it worked correctly before doing the update and found that the record count was wrong. Instead of 298 records, I had 2026 records. In researching the problem, I found that if I had 6 records in each base table, I ended up with 36 in my query. I have had this happen before and gotten around it by using "group by" however in this case, where I want to update a field, that won't work. Any suggestions and/or explainations?

 

[genomon]

Can you post the SQL?

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[Maker1]

SELECT tblMonitors.[QD Num]

FROM tblCrossover INNER JOIN tblMonitors ON tblCrossover.[QD#] = tblMonitors.[QD Num];

 

[genomon]

I am trying to run an update query

The above SQL is a select query; that aside, maybe add a DISTINCT clause to the SELECT?

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[Maker1]

I can't do the update since the records are comming up so bizarre. Right now the query is built as you see it. When I did try to run an update query it asked me that it was about to update 2026 records.

 

[genomon]

So how did the DISTICT clause work out?

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[SkipVought]

DISTICT???

Got a head cold, genomon?

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Maker1]

It didn't. Still brought up 2026 records instead of the 296 it should have.

 

[genomon]

Oops - looks like my spelling stinct

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[dhookom]

Are either of these two fields the primary key in one of the tables:
tblCrossover.[QD#]
tblMonitors.[QD Num]



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SkipVought]

sorry to dis you. ;-)

good comeback!

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Maker1]

They are not Primary keys, in either table.

 

[dhookom]

Ok, so they aren't primary keys. Then, are there duplicates of each field in the tables or is either of these at least unique? Are you sure they link 1 to 1? If they are 1 to 1 then they should be unique and should be prime candidates a a primary key or at least a unique index.

Can you reply again with the SQL view of your query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]