Question Tick boxes for brands / products ordered by each customer (1 Viewer)

[ascaife]

Hi all.

Please excuse my ignorance, I'm a bit new at this.

I'm setting up a fairly simple customer database for a fashion wholesaler. We have a set of customers who will be in tblClients, and a set of brands in tblBrands. I would like to have the ability in a form to view all the contact details for each record, as well as have two areas for tick boxes for current brands ordered by that customer, and potential brands for that customer.

I know I can just add each of the brands to tblClients and create a yes/no field, but that would create a maintenance nightmare when new brands are added to the portfolio.

Any help would be greatly appreciated.

Many thanks,

Andrew

 

[Steve R.]

You will need to review normilization. Essentially you will need a unique value for each customer and brand that would be used to build an order history table.

 

[ascaife]

Hi Steve,

Thanks for your reply, I appreciate it. I guess I didn't explain myself very well.

We're not looking to book orders into the system, we just want a list of brands to appear as tick boxes (currently 12 brands), and for each customer profile we would like to tick the brands that are normally ordered by that customer. The idea being that we can then print reports out for each brand listing all the customers for that brand.

Ideally the 12 brands would be listed in a continuous subform (I think) so that when a brand is added, we can immediately start ticking off customers without having to go in and edit the form.

Thanks,

Andrew

 

[WayPay]

Steve was still right about normalization . You're turning an interface problem into a database problem, which (as you noted) would create a maintenance nightmare.

This would be the basic normalized table structure:
Table Customer:
CustomerID (primary key)
Customer Name
..other customer-related data

Table Brand
BrandID (primary key)
Brand Name
..other Brand data

Table CustomerBrand
CustomerID (primary key)
BrandID (primary key)

You might just want to get on with it now, but learning about normalization will save you time in the long run. It's like stopping off at a footwear shop 5 minutes into running a marathon to ditch your platform boots for sneakers; it might leave you behind a bit right now, but you'll reach the finish faster and your feet won't be as sore the next morning .

 

[ascaife]

I actually got that far yesterday but couldn't work out where to next. I'm fine with the relationships and normalisation side of things, just not sure how to pull it together on the form.

 

[ascaife]

I should also note, I have worked out how to add the subform as datasheet and select each brand that applies from drop down lists, but what we really want is for all 12 brands to be displayed as tick boxes so we can quickly select each that applies per customer profile.

Thanks,

Andrew

 

[Steve R.]

I get the sense that you may be asking how to programatically create with VBA an "option group". If true, that approach would appear to require advanced programing skills that are beyond my capability. I am working on it.

The simple approach is to create a brand table. The brand table would need two additional logical fields "selected" and "active". You would then use SQL to populate the datasheet with the "active" data from the brand table

Checking the "selected" field associated with each brand would be equivalent to your "tick box"
The "active" field resolves the issue of whether that brand is or is not in business. I have used this approach for a mail merge program that I have.

 

[WayPay]

Andrew,

this should go a long way towards what you want. Yes, it looks ugly. Yes, it could be done without the buttons. Just not by me at this time . Thanks go to Steve W for inspiration.

 

[ascaife]

You guys rule, that is exactly what I was after.

Thank you so much for the assistance.