For Each - Record Add

[Moonshine]

Hi All, i think its going to be a For Each statement, but i could be wrong

Im setting up a small database, its got 2 tables at the moment, table 1 is Customer Details. Holds Name, Address, Contact Details and the like. Table 2 is a "Sport" table, basically lists the type of sports that the customer likes.

On the main customer add form there are 12 tick boxes, if the customer likes say Snowboard, Skiing and BMX'ing you tick those three boxes. If the customer like 7 sports, you stick 7 boxes and so on...

What i need to be able to do is cycle through those 12 boxes and add a seperate record to a 3rd table. This table holds the Unique Customer id, and all the sports the customer likes.

So, table 1 would have 1 record with its unique Customer ID. Table 2 could have 3 records for sports, containing the unique Customer ID and a Unique "Sport" ID.

Is a For Each the best way to add each record to Table 2? All the check boxes have "Sport" in their name, so it could be a For Each chk Like(*Sport*) or something?

 

[Mile-O]

Im setting up a small database, its got 2 tables at the moment, table 1 is Customer Details. Holds Name, Address, Contact Details and the like. Table 2 is a "Sport" table, basically lists the type of sports that the customer likes.

Your design is completely wrong. It sounds as if the second "sport" table is designed with fields like this:

CustomerID, SPort1, SPort2, SPort3, SPort4, ......, Sport11, Sport12

WRONG!

This is the structure you should need.

tblCustomer
CustomerID (Autonumber, Primary Key)
Forename (Text)
Surname (Text)
Address1 (Text)
Address2 (Text)
TelNumber (Text)
MobNumber (Text)
etc...

tblSports
SportID (Autonumber, Primary Key)
SportName (Text)

tblCustomersToSports
CustomerID (Number, Primary Key)
SportID (Number, Primary Key)


You have jumped too early into forms. I'd suggest binning the form you have and starting again - this time with a form and subform combination. A query of the customer table is bound to the parent form while the subform is bound to a query of the customers to sports table. These two forms are then linked by the the related field in both: the CustomerID.

 

[Moonshine]

Thanks for your reply, but its not correct

Table 1 (Customer) is set up like you have it.

Table 2 (Sports) is set up like you said in your reply. It has Sport ID and Sport Name

Table 3 (Customer > Sports Relationship) has Reference Number (Unique for that record in that table), Customer ID and Sport ID

That's how its set up, which i know is correct

After i posted it, i did look at the form and think.. hrmm it would work better with subforms...

 

[Mile-O]

What do you need the reference field for? The SportID and CustomerID both set as the table's composite primary key will guarantee uniqueness i.e. one of each sport per each customer. The reference field seems unnecessary and excessive.

 

[Moonshine]

Ive always set them up with a Unique Record, its not necessary like you say, but i like to have one Hmm, but now you mention it i might get rid of it. As you say its really not needed, and this is 1 table of 9 (Sport, Clothing, Eyewear, Footwear etc) similar ones.. so its alot of extra for no reason.

 

[boblarson]

It's not necessary to have the added field to force it to be unique. By using a composite primary key (CustomerID and SportID) then I'm guaranteed that I can enter a sport only ONCE for a certain customer. So, blow away the "Reference" number and in the design view of the table, select both CustomerID and SportID and click on the Primary Key button to set your composite key.

 

[Moonshine]

Thanks for all the advice, im going to do some work on it today so i will take all your points and try to get it to work

As for what the user insisted, that doesnt come into it. I think the exact spcification was "A Database for my shop" lol