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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|