Not sure what title to put...!! Query from Yes/No fields...

[CarysW]

Hello,

I am creating 'searches' for users using queries and custom parameter forms.

I have a large list of customers and the brands they take from us, I want to create a query and form to search for stockists of certain brands in certain areas(regions, counties etc).

Now the easy solution would be to have a field called Brand in the Master table and then have the brand they stock listed in that field or simply even an index number, the trouble is that most customers buy more than one brand so this wouldn't be possible.

At the moment my master table has a field for each brand with Yes/No fields and checkboxes.

Does anyone have any suggestions as to how I can approach this?

I'm using Access 2007.

 

[pbaldy]

I would correct (normalize) the design by getting rid of the repeating brand fields in the main table, and adding a many-to-many junction table. If a customer handled 5 brands, they would have 5 records in that table. Fields would include CustomerID from the customer table and BrandID from a brands table.

 

[CarysW]

Thanks Paul, I feared that would be the case. Could you tell me how I would go about creating the query after I've done this?

 

[pbaldy]

I'm not clear on what the query should do. Can you clarify that, and maybe post a sample db? Generally speaking, querying the junction table with joins to the others should allow you to search on pretty much anything.

 

[The_Doc_Man]

Something you need to know: If you define the relationships correctly between a parent and a child table, then use the query-builder grid on the two related tables, Access will automagically build the right JOIN query for you.

Next, you need to consider who is the parent and who is the child, but this looks like a junction-table case anyway.

tblCustomers, CustID (prime key), name, address, telephone number, etc etc etc

tblBrands, BrandID (prime key), name, other brand-only data

tblCustBrnd, CustID (foreign key to tblCustomers), BrandID (foreign key to tblBrands), other data that relates to the combination of customer and brand.

The tblCustBrnd is called a junction table. If customer 1 buys brand X and Y but customer 2 buys brand X and Z, you would have

tblCustBrand: (1,X), (1,Y), (2,X), (2,Z), etc etc

Then you have to ask, am I trying to see who buys the brands or what brands the customers buy? (That is really two different questions, though the difference might be subtle if you aren't used to thinking this way.) See, if you are looking for a list of customers who buy brand X, then you GROUP by the brand ID and the customer ID becomes a detail element. If you are looking for the brands used by customer 1, then you GROUP by the customer ID and the brand ID becomes the detail element.

Do some serious reading on normalization and some problem analysis to see what you need to break out in various reports and queries. This is not me giving you off-handed advice, either. You need to know a couple of "Old Programmer Rules."

1. Access won't tell you anything you didn't tell it first.

2. If you can't do it on paper, you can't do it in Access.

#1 is a way of saying in a round-about way that if you want to see something in a report, it had better be part of what you provide to Access so that Access can PUT it in that report. In other words, work BACKWARDS from the problem requirements to get the data requirements.

#2 says that if you cannot draw out the data flow of the operation by hand with paper and pencil (or dry-erase board and suitable markers), you aren't ready to implement anything in Access yet. Because Access doesn't "know" how to do anything that you don't know how to tell it to do. (Not counting aggregate functions... you can tell it take sums or averages. You just have to know WHICH things to sum or average.)

 

[CarysW]

Thanks The Doc Man that's really helpful.