Relationships and Reporting

[Guest]

Ok....Here we go....

I have a Table called tblCompanyClient.....which are the Clients for a
certain Company. So obviously I have a one-to-many reltionship to tblCompany.

Now, within the tblCompanyClient table I have built 5 buckets for the
products that the Client might have. I realize this is not formally
normalized, but I didn't think I needed to build an additional table for that
relationship. These 5 buckets are then linked back to the tblCompanyProducts
table. When I defined this relationship, Access seemed to automatically
create 4 additional tables...tblCompanyProducts_1,_2,_3, and _4.

Now I need to report on the product name that is within tblCompanyProducts.
The problem is that those dynamically created tables are NOT in my drag down.
So I can only choose chrProductName only one time.

Can anyone help me here???

Thanks in advance....

 

[MacDermott]

Normalize your data and this problem will be much easier to address.
Yes, this will require another table, but that's not something to be avoided
in and of itself.

 

[John Vinson]

When I defined this relationship, Access seemed to automatically
create 4 additional tables...tblCompanyProducts_1,_2,_3, and _4.

Now I need to report on the product name that is within tblCompanyProducts.
The problem is that those dynamically created tables are NOT in my drag down.
So I can only choose chrProductName only one time.

It's actually four additional *icons on the screen*, aliases for the
same table. There's only one table.

If you don't follow McDermott's excellent advice to normalize (and I
hope you will, it's your best solution), then you need to create the
query by adding tblCompanyProducts to the query *five times*; Access
will alias the additional instances by adding _1, _2 etc. to the name
(but it's still just one table). Join each instance to a separate
field.

Then when you have a sixth product a few months down the road, you can
completely redesign your tables, queries, forms, and reports rather
than just adding a row... <g>

John W. Vinson[MVP]