Prev: Form Combo box - multiple Control Source(s)?
Next: How to extract data
From: DJH on 6 Apr 2010 09:31
Access 2003 novice user. I am attempting to build a report using the
wizard. Table "a" has a field "barcode". Table "B" has 4 fields
"barcode1", "Barcode2", etc. I am attempting to identify the
associated name on the same row of the barcode fields in table B with
the matching barcode field in table A. I thought I built a one to
many relationship, but when I run the report it only matches one of
the fields in table "B", "barcode1" the primary key is system assigned
in "table A" What am I missing?
From: Salad on 6 Apr 2010 10:55
DJH wrote:

> Access 2003 novice user. I am attempting to build a report using the
> wizard. Table "a" has a field "barcode". Table "B" has 4 fields
> "barcode1", "Barcode2", etc. I am attempting to identify the
> associated name on the same row of the barcode fields in table B with
> the matching barcode field in table A. I thought I built a one to
> many relationship, but when I run the report it only matches one of
> the fields in table "B", "barcode1" the primary key is system assigned
> in "table A" What am I missing?

A link?

You might have a customer order. Order #1 in field OrdNum. The order
details would have a field called OrdNum as well that would provide the
link to both tables. Thus if I set a relationship between the two
tables and there were 3 items ordered, I should see 3 rows for Order 1.

If you have a similar setup, you need to also select the fields for
Barcode1...4, not just Barcode1.

Or you code have a field called BCID in table A and then link to table
on BCID and each record have a field called BarCode.
From: paii, Ron on 6 Apr 2010 11:49

"DJH" <dhermus(a)aol.com> wrote in message
news:f02aad13-14c2-4fdd-9165-9a52382bca7a(a)u31g2000yqb.googlegroups.com...
> Access 2003 novice user. I am attempting to build a report using the
> wizard. Table "a" has a field "barcode". Table "B" has 4 fields
> "barcode1", "Barcode2", etc. I am attempting to identify the
> associated name on the same row of the barcode fields in table B with
> the matching barcode field in table A. I thought I built a one to
> many relationship, but when I run the report it only matches one of
> the fields in table "B", "barcode1" the primary key is system assigned
> in "table A" What am I missing?

Because Table B's structure is not normalized, the wizard will not work. You
will need to edit the query, adding a reference to Table A for each barcode
field in Table B.

Example: Assuming 4 barcodes fields in Table B I used left join, assuming
some of your barcode fields are blank

SELECT TableB.Barcode1, TableA.Descriptions AS Desc1, TableB.Barcode2,
TableA_1.Descriptions AS Desc2, TableB.Barcode3, TableA_2.Descriptions AS
Desc3, TableB.Barcode4, TableA_3.Descriptions AS Desc4
FROM (((TableB LEFT JOIN TableA ON TableB.Barcode1 = TableA.Barcode) LEFT
JOIN TableA AS TableA_1 ON TableB.Barcode2 = TableA_1.Barcode) LEFT JOIN
TableA AS TableA_2 ON TableB.Barcode3 = TableA_2.Barcode) LEFT JOIN TableA
AS TableA_3 ON TableB.Barcode4 = TableA_3.Barcode;

IMO: Normalizing Table B would make your work easer


 | 
Pages: 1
Prev: Form Combo box - multiple Control Source(s)?
Next: How to extract data