A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

tables and relationships



 
 
Thread Tools Display Modes
  #1  
Old September 25th, 2008, 01:28 PM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 22
Default tables and relationships

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


  #2  
Old September 25th, 2008, 02:19 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default tables and relationships

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.



" wrote:

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



  #3  
Old September 26th, 2008, 09:23 AM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 22
Default tables and relationships

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)



  #4  
Old September 26th, 2008, 02:21 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default tables and relationships

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.