relationship/report question

[Jessica]

Hello

I am confused as how relationships work and wanted to see if I have it
right with the database I am working on now. I have a products table and
a labels table. One record in the products table can have a front label
and a back label which are two separate records in the labels table. Two
records in the labels table can belong to more than two records in the
products table (this is because I have a record in the labels table with
a value No back label which may apply to many records in the products
table. So with my tables setup that way I made a junction table to link
the two. Is this the right path? If so for my report I need to list the
front and back label in separate fields according to my primary key in
the products table. I can get one label to show I think the first one of
the two in the labels table but can't get the second.

I hope I didn't make it sound too confusing.

TIA,
Jess

 

[David S via AccessMonster.com]

Hello Jessica,

How does your Labels table distinguish between what is a Back and what is a
Front label? Is there an attribute there somewhere? Or do you use a link
table to define which ones are there? eg

Table Labels - fields LabelID, LabelText
Table Products - fields ProductID, ProductDescription

With this setup, there are a couple of ways to define which are the front and
back labels:
Table FrontLabel - fields ProductID, LabelID
Table BackLabel - fields ProductID, LabelID

or
Table ProductLabels - fields ProductID, FrontLabelID, BackLabelID

or
Table Products - apart from the fields above also has FrontLabelID,
BackLabelID

 

[Jessica]

Hi David

As I have it setup now
Table Products - fields UPC, etc...
Table Labels - fields item number, label description
Junction Table - fields UPC, item number

Usually the first item number of each label is the front. When I build a
query it lists both label item numbers. I just don't know how to get the
second item number to show in my report.

I hope that clears it up better,
Jess

 

[JACY FONSECA]

Hello

I am confused as how relationships work and wanted to see if I have it
right with the database I am working on now. I have a products table and
a labels table. One record in the products table can have a front label
and a back label which are two separate records in the labels table. Two
records in the labels table can belong to more than two records in the
products table (this is because I have a record in the labels table with
a value No back label which may apply to many records in the products
table. So with my tables setup that way I made a junction table to link
the two. Is this the right path? If so for my report I need to list the
front and back label in separate fields according to my primary key in
the products table. I can get one label to show I think the first one of
the two in the labels table but can't get the second.

I hope I didn't make it sound too confusing.

TIA,
Jess

 

[David S via AccessMonster.com]

It does, and you're going to have a problem in that you have no way of
distinguishing which label is the front and which is the back one from your
Junction table. You can get the first and last ones with a query like this:
SELECT [Junction].[UPC], First([Labels].[item number]) AS [FirstOfitem number]
, First([Labels].[label description]) AS [FirstOflabel description], Last(
[Labels].[item number]) AS [LastOfitem number], Last([Labels].[label
description]) AS [LastOflabel description]
FROM Junction INNER JOIN Labels ON [Junction].[item number]=[Labels].[item
number]
GROUP BY [Junction].[UPC];

But this assumes that the Front label is always the first record in the
Junction table and that the Back label is always the last. This sounds like
it's true, so it sort of works - but the query will have problems if you
start introducing say, a Top label or a Side label. If a product only has one
label, it will show that as both the Front and the Back label. If you need to
be more precise, you really need to add a "label location" column to your
Junction table.

If you only want the Back label if it's different from the Front one, then
I'd add the label count to the query above (let's call it [Product Labels]:
SELECT Junction.UPC, First(Labels.[item number]) AS [FirstOfitem number],
First(Labels.[label description]) AS [FirstOflabel description], Last(Labels.
[item number]) AS [LastOfitem number], Last(Labels.[label description]) AS
[LastOflabel description], Count(Junction.[item number]) AS [CountOfitem
number]
FROM Junction INNER JOIN Labels ON Junction.[item number] = Labels.[item
number]
GROUP BY Junction.UPC;

and then do some checks on it:
SELECT [Product Labels].UPC, [Product Labels].[FirstOfitem number] AS [Front
Label Number], [Product Labels].[FirstOflabel description] AS [Front Label
Description], IIf([CountOfitem number]>1,[LastOfitem number],"") AS [Back
Label Number], IIf([CountOfitem number]>1,[LastOflabel description],"") AS
[Back Label Description]
FROM [Product Labels];

This query also renames the columns to make them more intelligble...