Newbie -Selecting a DISTINCT 2 fields out of 3?

[Vet Tech]

I'm using A2003 to create a DB to handle uniforms. Earlier my
categories table (tblCategories) had only 2 fields ie Category and
Size so it was reasonably easy to use cascading combo boxes in a form
to select a specific category/size combination and input these in to
the main table.

Now I want to create a proper relational arrangement and link the
tblCategories to the main table by adding another field to
tblCategories called CatSizeID. This would be an autonumber primary
key and so be unique for each combination of Category and Size. So far
there are about 200 unique combinations from the table which has 20
categories and 15 sizes.

On a form, I want the users to continue to use the two combo boxes to
select first category from cmbCategory and then size from cmbSize so
that the corrrect CatSizeID is selected and used later as a
reference.

My problem is how to restrict the choice visible in cmbCategory to
show just the 20 unique categories and not and not the 200 from the
lines that make up the 200 combinations. Once the size is selected
then the aim is to establish the CatSizeID forthis combination.

I'm being stymied at pesent because SELECT DISTINCT gives me all 200
lines. Maybe I'm approaching it in the wong direction? Can someone
assist please.

 

[Tom van Stiphout]

On Thu, 11 Dec 2008 02:50:04 -0800 (PST), Vet Tech

It sounds like your db design is still incorrect. It appears you have
a many-to-many (M:M) between categories and sizes: each category has
many sizes, and each size occurs in many categories. To express this,
you need 3 tables:
tblCategories
CatID (PK)
CatName (UIDX)

tblSizes
SizeID (PK)
SizeName (UIDX)

tblCatsWeSell
CatID (PK)
SizeID (PK)

Once this is in place, you want two dropdowns. #1 is simply bound to
"select * from tblCategories". #2 is bound to:
select tblCatsWeSell.SizeID, tblSizes.SizeName
from tblCatsWeSell inner join tblSizes on tblCatsWeSell.SizeID =
tblSizes.SizeID
where tblCatsWeSell.CatID = Forms!yourForm!yourFirstCombobox

In #1's AfterUpdate event you write:
yourSecondCombobox.Requery

This is untested code, but it should be close.

-Tom.
Microsoft Access MVP

 

[Vet Tech]

Tom,

I see the logic behind that now and that's cleared my thinking
appreciably.

Just one query.......... will this work when the different size ranges
apply to different categories eg jackets, berets and belts all have a
different size ranges?

VT

 

[Tom van Stiphout]

On Thu, 11 Dec 2008 07:59:56 -0800 (PST), Vet Tech

Try it, and you will find out: YES!

-Tom.
Microsoft Access MVP

 

[Vet Tech]

Tom,

I must have been having several protracted ‘senior moments’ all rolled
into one as I've been battling with this for the last few days and not
quite got to the end of it.

I have reworked the design to have 3 tables:-

tblCategories
CatID (PK)
Category

tblSizes
SizeID (PK)
Size

tblKitList
CatId (PK)
SizeID (PK)


I have created a form called frmNewCatSize and made two combo boxes-
cmbCategory and cmbSize - each with 2 columns with the first column
hidden and bound to the respective ID field in tblKitList. So Far so
good.

I set up cmbCategory with Row Source .. ...SELECT tblCategories.CatID,
tblCategories.Category FROM tblCategories ORDER BY
tblCategories.Category; and have its AfterUpdateEvent to be
Me.cmbSize.Requery. I had an initial problem with Not in List but
fixed with code I got from the web. So I can now create a new Category
name.

The problem is with the second combo ie cmbSize. It appears to have a
problem with the Row Source statement. This is currently …….

SELECT tblKitList.SizeID, tblSizes.Size FROM tblKitList INNER JOIN
tblSizes ON tblKitList.SizeID=tblSizes.SizeID WHERE
tblKitList.CatID=Forms!frmNewCatSize!cmbCategory;

When I put in the new Size eg ’Large’, I get the error message…..

The record source ‘~sq_cFrmNewCatSize~sq_ccmbSize’ specified on this
form does not exist’.etc, etc..

If I escape there and check the tables, I find ‘Large’ appears in the
tblSize but the SizeID does not appear in tblKitList alongside the
CategoryID where presumably it should be.

I’d be most grateful for further assistance.
VT