tables and relationships

  • Thread starter Thread starter fiona.innes
  • Start date Start date
F

fiona.innes

Hiya,

I have a database that is going to be used for a car garage ideally
what id like to do is to have a table customers
with fields .. tblCustomers

(...IDCustomer (automatic No & PK)
strCustomerName
strAddress
strContactName
strContactNo....)

then another table which will contain all the vehicle details, each
customer has many vehicles tblVehicle

(..VehicleReg (This will be the PK)
strMilrage
strLastService
strChassieNo
strAdditionalInfo
strCustomerName (this will be the FK)...)

to this each vehical can have many repairs/jobs carried out on it i.e
tblJob

(..JobID .. (Automatic No & PK)
VehicleReg (Foregin Key)
strJobDetails
strDateWorkDone
strCost...)

When a job is carried out various parts are sometimes ordered so i
have a parts table tblParts

(..PartID (Automatic No & PK)
strPartName
strNetCost
strRetailCost
strSupplier
JobID)

I have created these tables but when I make all the replationships the
do not come up as 1 to many and when I had data to the tables via
forms the data does not save into the tables. Can anyone give me some
advice as to what I can do to make this work and/or improve my tables.


Thanks

Fiona
 
I'm assuming that it was a typo that in your second table you listed the
CustomerName as the FK rather than IDcustomer. Other than that your table
structure looks good.

So your problem is probably in the relationships. How you define them, how
and when you create them and want to use them. That part you really haven't
told us anything about.
 
Yes sorry that was a typo.
What I have is a main for frmMain when a user can enter a new
customer, a new vehicle and a new job.
When I enter in a new customer or/and vehicle that works fine but when
I go to enter in a new job it allows me to
enter the details but doesnt save. I have all the tables linked
together as 1 to many relationships.
Should I have a subform within the vehical form (fmVehicle)
 
There may be someone else reading this who can guess at your problem from the
info given or more directly tell you exactly what to do, or provide more
expert comments on my less expert comments. But if I may give a few
thoughts & ideas:

Some type of a subform structure would really be the way to go. I have a
hard time imaging a display/form that doesn't use this. Probably the the
individual tables as the record sources.


You probably did this already, but double check that all of your PK fields
have the Access setting as PK's.

Whe you say "Doesn't save" do your mean that you get an error message? Or
just that it becomes invisible on the form (even if it did get saved). Per
below, it's possible that if you entered a record in a lower level table, the
nature of the joins maye make that record invisible in a query record source
for your form.


Not sure what the record source of your form is. I'm assuming that it's
some type of query with all three tables? You also may want to check
directly if that record sources is editable in the are that you're having a
problem.

The "One to many" term really relates more to the nature of the data than of
the join. I would take a close look at the details of the joins in whatever
your record sources for the form is and make sure that they are the type.


Fred
 
Back
Top