One-to-Many Query

K

Karen

I'm not sure I want to be using a query to do this but I'll put it here
anyway...

I have two tables that I need to reference to print a Certificate.

The first table is FillRecord (the fields are productno, productlot,
bulkno, bulklot, etc). In this table we record the use of the Bulk so
a Bulk will appear in several Products.

productno productlot bulkno bulklot
2209-L 425425 2209 425
2209-L 425501 2209 425
2786-E 4051212 2505 405
2786-H 106331 2505 106

The second table is the QualityRecord for the Bulk. In this table we
record the production of the Bulk (the fields include bulkno, bulklot,
madedate, etc).

bulkno bulklot madedate
2505 106 04/10/06
2505 405 09/15/06
2209 425 09/28/05

What I want to do is have the user enter the productno and the
productlot into a dialog form and get (print) a certificate for the
Bulk used to fill the Product. The certificate is a report with the
query as its record source.

My current query is as follows:
SELECT tblfillrecord.productno, tblfillrecord.bulklot,
tblfillrecord.bulkno, tblfillrecord.productlot,
tblQualityRecord.madedate
FROM tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.bulkno
= tblQualityRecord.bulkno) AND (tblfillrecord.bulklot =
tblQualityRecord.bulklot)
WHERE (((tblfillrecord.productno) Like [forms]![frm cert dialog
box]![text0]) AND ((tblfillrecord.bulklot)=IIf(Len([forms]![frm cert
dialog box]![text2])=Val(8) Or (Len([forms]![frm cert dialog
box]![text2])=Val(7) And Mid([forms]![frm cert dialog
box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4),Left([forms]![frm cert dialog box]![text2],3))));

With this I get 2 records for 2209 when I only want one.


Thanks for any help you can give me.

Karen
 
K

Karen

the subject should have been Many-to-One. My fingers move faster than
my brain sometimes.
K-
 
J

John Spencer

If you don't need ProductLot in the results you could use DISTINCT in the
query and drop ProductLot from the list of fields to display.
SELECT tblfillrecord.productno
, tblfillrecord.bulklot
, tblfillrecord.bulkno
, tblfillrecord.productlot
, tblQualityRecord.madedate
FROM tblfillrecord INNER JOIN tblQualityRecord
ON (tblfillrecord.bulkno = tblQualityRecord.bulkno)
AND (tblfillrecord.bulklot = tblQualityRecord.bulklot)
WHERE (((tblfillrecord.productno) Like
[forms]![frm cert dialog box]![text0]) AND
((tblfillrecord.bulklot)=IIf(Len([forms]![frm cert dialog
box]![text2])=Val(8)
Or (Len([forms]![frm cert dialog box]![text2])=Val(7)
And Mid([forms]![frm cert dialog box]![text2],4,1)<>'1'),
Left([forms]![frm cert dialog box]![text2],4),
Left([forms]![frm cert dialog box]!text2],3))));

If you need ProductLot but it doesn't make any difference which of many you
return, then use totals query and group by all the fields except productLot.
Use Max, Min, First, or Last on ProductLot

If you need to list all the product lots for the quality record, then you
can have multiple records or check out Duane Hookom's concatenate function.

Karen said:
I'm not sure I want to be using a query to do this but I'll put it here
anyway...

I have two tables that I need to reference to print a Certificate.

The first table is FillRecord (the fields are productno, productlot,
bulkno, bulklot, etc). In this table we record the use of the Bulk so
a Bulk will appear in several Products.

productno productlot bulkno bulklot
2209-L 425425 2209 425
2209-L 425501 2209 425
2786-E 4051212 2505 405
2786-H 106331 2505 106

The second table is the QualityRecord for the Bulk. In this table we
record the production of the Bulk (the fields include bulkno, bulklot,
madedate, etc).

bulkno bulklot madedate
2505 106 04/10/06
2505 405 09/15/06
2209 425 09/28/05

What I want to do is have the user enter the productno and the
productlot into a dialog form and get (print) a certificate for the
Bulk used to fill the Product. The certificate is a report with the
query as its record source.

My current query is as follows:
SELECT tblfillrecord.productno, tblfillrecord.bulklot,
tblfillrecord.bulkno, tblfillrecord.productlot,
tblQualityRecord.madedate
FROM tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.bulkno
= tblQualityRecord.bulkno) AND (tblfillrecord.bulklot =
tblQualityRecord.bulklot)
WHERE (((tblfillrecord.productno) Like [forms]![frm cert dialog
box]![text0]) AND ((tblfillrecord.bulklot)=IIf(Len([forms]![frm cert
dialog box]![text2])=Val(8) Or (Len([forms]![frm cert dialog
box]![text2])=Val(7) And Mid([forms]![frm cert dialog
box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4),Left([forms]![frm cert dialog box]![text2],3))));

With this I get 2 records for 2209 when I only want one.


Thanks for any help you can give me.

Karen
Click to expand...
 
K

Karen

I dropped the ProductLot and used SELECT DISTINCT and it worked
perfectly. I was thinking I needed to use the Unique Values property
of the query but didn't see that ProductLot was holding me back from
the desired result. ProductLot is not necessary on the certificate so
I did away with it in the query.

Thanks for the help!

Karen



John said:
If you don't need ProductLot in the results you could use DISTINCT in the
query and drop ProductLot from the list of fields to display.
SELECT tblfillrecord.productno
, tblfillrecord.bulklot
, tblfillrecord.bulkno
, tblfillrecord.productlot
, tblQualityRecord.madedate
FROM tblfillrecord INNER JOIN tblQualityRecord
ON (tblfillrecord.bulkno = tblQualityRecord.bulkno)
AND (tblfillrecord.bulklot = tblQualityRecord.bulklot)
WHERE (((tblfillrecord.productno) Like
[forms]![frm cert dialog box]![text0]) AND
((tblfillrecord.bulklot)=IIf(Len([forms]![frm cert dialog
box]![text2])=Val(8)
Or (Len([forms]![frm cert dialog box]![text2])=Val(7)
And Mid([forms]![frm cert dialog box]![text2],4,1)<>'1'),
Left([forms]![frm cert dialog box]![text2],4),
Left([forms]![frm cert dialog box]!text2],3))));

If you need ProductLot but it doesn't make any difference which of many you
return, then use totals query and group by all the fields except productLot.
Use Max, Min, First, or Last on ProductLot

If you need to list all the product lots for the quality record, then you
can have multiple records or check out Duane Hookom's concatenate function.

Karen said:
I'm not sure I want to be using a query to do this but I'll put it here
anyway...

I have two tables that I need to reference to print a Certificate.

The first table is FillRecord (the fields are productno, productlot,
bulkno, bulklot, etc). In this table we record the use of the Bulk so
a Bulk will appear in several Products.

productno productlot bulkno bulklot
2209-L 425425 2209 425
2209-L 425501 2209 425
2786-E 4051212 2505 405
2786-H 106331 2505 106

The second table is the QualityRecord for the Bulk. In this table we
record the production of the Bulk (the fields include bulkno, bulklot,
madedate, etc).

bulkno bulklot madedate
2505 106 04/10/06
2505 405 09/15/06
2209 425 09/28/05

What I want to do is have the user enter the productno and the
productlot into a dialog form and get (print) a certificate for the
Bulk used to fill the Product. The certificate is a report with the
query as its record source.

My current query is as follows:
SELECT tblfillrecord.productno, tblfillrecord.bulklot,
tblfillrecord.bulkno, tblfillrecord.productlot,
tblQualityRecord.madedate
FROM tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.bulkno
= tblQualityRecord.bulkno) AND (tblfillrecord.bulklot =
tblQualityRecord.bulklot)
WHERE (((tblfillrecord.productno) Like [forms]![frm cert dialog
box]![text0]) AND ((tblfillrecord.bulklot)=IIf(Len([forms]![frm cert
dialog box]![text2])=Val(8) Or (Len([forms]![frm cert dialog
box]![text2])=Val(7) And Mid([forms]![frm cert dialog
box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4),Left([forms]![frm cert dialog box]![text2],3))));

With this I get 2 records for 2209 when I only want one.


Thanks for any help you can give me.

Karen
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with code - DAO Recordset 7
catching an error opening a form with a query as the recordsource 1
QUERY TO SORT BY MORE THAN ONE COLUMN 1

Top