A few questions regarding a new database...

[ChrisSedgwick]

Hi All,

I'm designing a new database that will be used in the company that I work for and hopefully assist with our installations department.

The purpose of the database will be to record what we call 'F Notes', which is basically a Purchase Order, only that we use a unqiue number that begins with an 'F'. Pretty simple!

I'm putting all my tables/fields together (on paper first) to figure out what I'm going to need and what kind of relationships to set up etc etc. However, I've come across a small issue that I'm hoping you can resolve. Here are the tables so far, before I explain:

tblFitters:
FitterID
FitterName
ContactNo
Email

FNotes:
FNoteID
FitterID
Date
JobNo
OrderType
ContractorID
ContractManager
ContractName
ContractAiteAddress
SiteContact

Contractors:
ContractorID
ContractorName

Now here's my dilema. On any specific job, there could be up to 4 fitters. I'd want to show the 4 fitters on the purchase order. I had an idea to use a Combo Box in my form that would store the Fitter as text in the relevant table. However, how would I store more than one?

I've thought about creating 4 seperate fields in the table i.e:

Fitter 1
Fitter 2
Fitter 3
Fitter 4

and just having 4 combo boxes in the form to display them all that way, however thought I'd check to see if I was missing something, or there was an alternative way. Would doing it this way still allow me to include them on a purchase order that'll create as a report?

Any help or suggestions would be greatly appreciated. I haven't created the database yet, It's all just going down on paper. So I'm able to change anything at all that I need to in order to make it effective.

Many thanks in advance,

Chris.

 

[ChrisSedgwick]

Hi,

To add to my original post. The fitters wqould link via the FitterID. So how would I be able to include more fitters in the table?

Even if I set up Fitter 1,2,3,4 in their own respective fields I would only be able to use a combo box to store the text back in the table. I wouldn't be able to store it by the FitterID, because it would only allow me to use the FitterID field once in the relationship manager.

Need help on this one?

 

[gemma-the-husky]

fNotes sounds a strange name, but that's by the by

anyway, if you need multiple fitters then you cannot store a single fitterid in the fnotes table. You need a separate table to store all the fitters for that order.

you might be able to change the fitterID field to a "multiple values field" (a new data type) which effectively manages the extra table for you behind the scenes. The downside is that this construct traps you into using access, as the MVF is not available in SQL Server etc.

 

[ChrisSedgwick]

Hi gemma-the-husky,

Thanks for your reply. I've not had any experience with SQL so would this cause a problem. Is that the only work-around for me to be able to add more than 1 fitter?

 

[ChrisSedgwick]

gemma-the-husky,

I think I understand what you mean now. So I've created a new table to accomadate 4 fitters:

tblFittersOrders (not quite sure on the choice of name):
FitterOrderID
Fitter1
Fitter2
Fitter3
Fitter4

Can you see anything else that I would need to include in that table?

Thanks,

 

[plog]

Close but no. You have a many to many relationship between FNotes and tblFitters. To accommodate that type of relationship you create a junction table: http://en.m.wikipedia.org/wiki/Junction_table

tblFittersOrders should have this structure:

TblFittersOrders
FitterOrder_ID, auto number primary key of table
FNoteID, number, foreign key to FNotes
FitterID, number, foreign key to tblFitters

That's it. Now you can assign the exact number of fitters to a note--1, 4, or even 400.