Relationships & update of records

[Mark-BES]

Hello all,

I am in the early stages of building my 1st relational database (see attached). Ones I have built in the past were built on a knowledge of the sample ones provided with A97. I have since realised (from this excellent site) everything I have done in the past was completely wrong! One big table to hold all info causing hundreds of duplicate records - Doh!

My Db is being designed to keep a record of all the refurbished dictation/transcription stock we hold.

I have a question or two (basic stuff but i'm learning (slowly)).

1. Do my relationships look ok so far?

2. I want user to simply select a model in "Tbl_UnitDetail", and some how automatically lookup the rest of the info such as make and type from "Tbl_unit"
New models will be added to "Tbl_unit" by administrators as and when they become available. How can this be done?

Any advise or comments on my limited knowledge would be greatly received. Many thanks.

 

[neileg]

I have a question or two (basic stuff but i'm learning (slowly)).

1. Do my relationships look ok so far?

Yes

2. I want user to simply select a model in "Tbl_UnitDetail", and some how automatically lookup the rest of the info such as make and type from "Tbl_unit"
New models will be added to "Tbl_unit" by administrators as and when they become available. How can this be done?

You shouldn't duplicate make, model and type in the Tbl_Unitdetail. A query linking Tbl_Unitdetail with Tbl_Unit will pull back this info. Just create a form for administrators to access. Worry about restricting this later!

Any advise or comments on my limited knowledge would be greatly received. Many thanks.

You have a field called Name in Tbl_Employee. Name is a restircted VB word and should not be used. Call it EmpName or something. Also, remove spaces from field names, otherwise you will need to enclose the field name in [] every time you use it.

I'n not sure about the 1 to many relationship between Tbl_refurb and Tbl_Unitdetail. I don't know what you are modelling. Will the same units be going in and out so that Tbl_Refurb can hold details of the same unit more than once?

 

[Mark-BES]

neileg, thankyou so much for your input.

I have made the changes you suggested.

Quote:
I'n not sure about the 1 to many relationship between Tbl_refurb and Tbl_Unitdetail. I don't know what you are modelling. Will the same units be going in and out so that Tbl_Refurb can hold details of the same unit more than once?

Yes, the machine may arrive from one cust, be reconditioned and be despatched to another at alater date.

Q: What is the best way to display various info from "TblRefurb" in a form?
I want to dispaly the actual customer details and model make/model/type etc. not the ID value which means nothing to the user.
Do I build a form based on a query or directly on the table? (See attached)
If so can you supply a very quick sample to get me going?

I know I am missing somthing really simple. All you help is most appreciated.

 

[neileg]

Always build your forms on a query, even if the query simply returns all of the data in a table. When you want to change the form, the query gives you vastly more flexibility with no downsides.

I've created a very simple form based on a query that joins tblUnit and tblUnitDetail. Hope this helps.

 

[Mark-BES]

neileg,

Your a star

1 more Q if I may?

I want to be able to have a Combo box in the form, so when I select the model from the list, it automatically displays the make and type (associated in TblUnit) in the other fields.

I have played around with the query, but I can only get it so it lists numbers (the UnitID) in the combo box.

Is this called a cascading combo box or am I confusing this with something else?

 

[neileg]

You need to base your combo on a query on tblUnit. Include all the fields in the query.

To do what you want, you don't really need to include tblUnit in your main query. When you build the combo, include the extra fields as columns in in the combo and then you can refer to these in unbound text boxes. See Access help for the syntax for this. If you don't want the fields to be visible in the combo to the user, set the column width for these fields to be zero.