too many records

  • Thread starter Thread starter jimbo jenkins
  • Start date Start date
J

jimbo jenkins

maybe i'm going about this the wrong way, but:

i've got a table (named TB_SN) with 1 field (named SN) containig
sequential list of serial numbers as text (e.g. 01_04_238), 30
records

this table is used primarily as a pick list in a form.
this form manipulates data in table DB_Checkouts (lots of fields, 20
records)

in my query i'm trying to end up with the 300 records from TB_SN wit
some extra text fields that i'm adding with an expression like th
following:

Designation:IIf([ASN]=[SN],"A",IIf([BSN]=[SN],"B","C"))

SN in TB_SN, ASN and BSN in DB_Checkouts
no problem here, this seems to work

THE PROBLEM:
I'm ending up with too many records. I'm getting 200 records for eac
of my 300 records (60,000 total). I can limit it to 150 or so by usin
In([ASN],[BSN]) as criteria for my SN field since there are 150 or s
matches. But that filters out the records in TB_SN that don't have
match in DB_Checkouts. I'm trying to get one record listed for each o
my records in TB_SN.

my logic is probably all bass ackwards here. if somebody can manage t
decipher what i'm trying to say here and has any suggestions please le
me know
thank
 
maybe i'm going about this the wrong way, but:
Click to expand...

I fear you are.
i've got a table (named TB_SN) with 1 field (named SN) containig a
sequential list of serial numbers as text (e.g. 01_04_238), 300
records

this table is used primarily as a pick list in a form.
this form manipulates data in table DB_Checkouts (lots of fields, 200
records)

in my query i'm trying to end up with the 300 records from TB_SN with
some extra text fields that i'm adding with an expression like the
following:

Designation:IIf([ASN]=[SN],"A",IIf([BSN]=[SN],"B","C"))

SN in TB_SN, ASN and BSN in DB_Checkouts
no problem here, this seems to work
Click to expand...
THE PROBLEM:
I'm ending up with too many records. I'm getting 200 records for each
of my 300 records (60,000 total).
Click to expand...

You are apparently including [TB_SN] and [DB_Checkouts] in a Query
without any JOIN line. This is exactly what you'll get in that case -
joining every record in each table to every record in each other
table.
I can limit it to 150 or so by using
In([ASN],[BSN]) as criteria for my SN field since there are 150 or so
matches. But that filters out the records in TB_SN that don't have a
match in DB_Checkouts. I'm trying to get one record listed for each of
my records in TB_SN.
Click to expand...

I THINK... not understanding the distinction between ASN and BSN makes
this a very shaky assumption!... that what you want is a Query joining
[TB_SN] to *two instances* of [DB_Checkouts]. Add the DB_Checkouts
table to your query twice; Access will alias the second instance to
DB_Checkouts_1. Join [SN] to [DB_Checkouts].[ASN], and to
[DB_Checkouts_1].[ASN]. Select each Join line and change it to a "Left
Outer Join" - "show all records in [TB_SN] and matching records in
[DB_Checkouts]".

BUT... the first thing you should do is reconsider your table
structure. Could you explain why Checkouts has *two* serial number
fields?
 
anybody?
Click to expand...

Yes, Jimbo. I replied yesterday; maybe you're not finding it in the
news server (I haven't visited ExcelTip.com so I'm not sure how it
presents your answers), so I'm copying my previous response to your
EMail. Please reply to the newsgroup; EMail support is for paying
clients.

maybe i'm going about this the wrong way, but:
Click to expand...

I fear you are.
i've got a table (named TB_SN) with 1 field (named SN) containig a
sequential list of serial numbers as text (e.g. 01_04_238), 300
records

this table is used primarily as a pick list in a form.
this form manipulates data in table DB_Checkouts (lots of fields, 200
records)

in my query i'm trying to end up with the 300 records from TB_SN with
some extra text fields that i'm adding with an expression like the
following:

Designation:IIf([ASN]=[SN],"A",IIf([BSN]=[SN],"B","C"))

SN in TB_SN, ASN and BSN in DB_Checkouts
no problem here, this seems to work
Click to expand...
THE PROBLEM:
I'm ending up with too many records. I'm getting 200 records for each
of my 300 records (60,000 total).
Click to expand...

You are apparently including [TB_SN] and [DB_Checkouts] in a Query
without any JOIN line. This is exactly what you'll get in that case -
joining every record in each table to every record in each other
table.
I can limit it to 150 or so by using
In([ASN],[BSN]) as criteria for my SN field since there are 150 or so
matches. But that filters out the records in TB_SN that don't have a
match in DB_Checkouts. I'm trying to get one record listed for each of
my records in TB_SN.
Click to expand...

I THINK... not understanding the distinction between ASN and BSN makes
this a very shaky assumption!... that what you want is a Query joining
[TB_SN] to *two instances* of [DB_Checkouts]. Add the DB_Checkouts
table to your query twice; Access will alias the second instance to
DB_Checkouts_1. Join [SN] to [DB_Checkouts].[ASN], and to
[DB_Checkouts_1].[ASN]. Select each Join line and change it to a "Left
Outer Join" - "show all records in [TB_SN] and matching records in
[DB_Checkouts]".

BUT... the first thing you should do is reconsider your table
structure. Could you explain why Checkouts has *two* serial number
fields?
 
Thanks for the help with JOIN John. That was what I was looking for..
except that by joining using all records from TB_SN and matchin
records from DB_Checkouts it appears the expressions i created in th
query no longer have access to the other fields in DB_Checkouts. Usin
a join where it only includes records if both are equal puts me bac
where i started with using the In([ASN,[BSN]) as Criteria. I'm thinkin
I may need to start over with this unless you have an idea.

John said:
BUT... the first thing you should do is reconsider your table
structure. Could you explain why Checkouts has *two* serial number
fields?
Click to expand...

To answer your question here, there are two serial number fields her
because a pair of identical parts are used when performing th
"Checkouts" that the table DB_Checkouts is storing records of.

thanks for your hel
 
Thanks for the help with JOIN John. That was what I was looking for..
except that by joining using all records from TB_SN and matchin
records from DB_Checkouts it appears the expressions i created in th
query no longer have access to the other fields in DB_Checkouts. Usin
a join where it only includes records if both are equal puts me bac
where i started with using the In([ASN,[BSN]) as Criteria. I'm thinkin
I may need to start over with this unless you have an idea.

John said:
BUT... the first thing you should do is reconsider your table
structure. Could you explain why Checkouts has *two* serial number
fields?
Click to expand...

To answer your question here, there are two serial number fields her
because a pair of identical parts are used when performing th
"Checkouts" that the table DB_Checkouts is storing records of.

thanks for your hel
 
Back
Top