[hqnet]

Hi,

I am triyng to build an Access 2003 application to manage our car repair shop, and I can't get the tables relations right.

This is what I am doing, I´ll be as simple and didactic as i can:

Info is collected around Cars, then Visits, then Repairs and Reports

Cars are Unique, identified by CarID number, but they will have several Visits, which in turn will have one, some, or all of the 5 available repair jobs performed in each visit.

This means that a CarID should be common for every entry of each car, but I should have a second identifier for the Visits, the repairs of THAT particular visit and the Report created for THAT particular Visit.

So far I have a primary table for the Car, a table for the visits, a table for each Repair and a table for each Report. I split it this way because the amount of items in each Repair/Table can be a bit large and having all of that in a single table would be hard to handle (IMHO) and it would mean unnecessarily having a lot of fields rarely used in the same table as the very frequent ones.

I have a parent form handling the Car´s basic data and subforms handling the rest of the tables, and they are "syncing" properly in terms of CarID and storing the data where they are supposed to.

But the problem is that I can´t create the sub-relation between "this-Visit" and "these-repairs-and-Report". (I should note that I´d prefer not to use dates to build relations and rather use IDs instead)

I did try using a VisitID that would be common to Visits and Repairs, this is the error I get and the way it is currently linked:

step 1: "Car form" is filled without problem
step 2: Focus on "Visit form" > it picks up CarID and auto creates VisitID correctly.
step 3: Focus on "Repair1 form" > it picks up CarID, but it does NOT pick up VisitID.
Leaving VisitID as default gives an error about a missing relation with "Visit form".
Manually entering the right VisitID works fine (AFAICT).


CarsTable
- CarID is Key and autonumeric

VisistTable
- VisitID is Key and autonumeric

Repair1Table (one for each Repair)
- Repair1ID is Key and autonumeric
- VisitID is numeric in the tables
- CarID is numeric in the tables

CarsTables -> VisitsTable // linked with CarID, enforce integrity
VisitsTable -> Repair1Table // linked with VisitID, enforce integrity

Previously I had linked everything with CarID AND VisitID but didn´t work either.


I would appreciate any help!
Thanks in advance and apologies for the long post.
HQ.

[midders]

You're duplicating data; CarID is only needed in the Car and Visit tables. You only need two, one to many, links:
tblCar.CarID = tblVisit.CarID
tblVisit.VisitID = tblRepair.VisitID

To get tblCar data from the RepairID backlink through tblVisit.

Slainte

midders

[OBP]

HQ, while I agree with what midders has said, I do have some concerns over your Table descriptions.
1. "Repair1Table (one for each Repair)" does this mean that you have 5 Tables? If so then this is not correct, you should have a Table of Repair Descriptions and then a table of Visit/Repairs, which should be a Many to Many design. ie One to Many on one side (Visits) and One to Many on the other side (Repairs).
2. "a table for each Repair and a table for each Report", does this mean that you are generating Tables to hold report data?
This is not advisable, as reporting data should be collected from the tables already in place using Queries.
3. You appear to have forgotten one of the most important elements in the Process by focusing on the "Car", which is the Customer. Especially as the Customer may have more than one car that you work on.

[hqnet]

First of all, thank you for the help

@midders
ok, I'll look into that

@OBP

1. "Repair1Table (one for each Repair)"
does this mean that you have 5 Tables? If so then this is not correct, you should have a Table of Repair Descriptions and then a table of Visit/Repairs, which should be a Many to Many design. ie One to Many on one side (Visits) and One to Many on the other side (Repairs).

I was trying to keep the description simple. The 5 "Repairs" are not 5 single items [i.e. A)Breaks, B)engine, C)paint] but rather categories with mutiple items [i.e. A) a1) front breaks a2) rear breakes a3) hand break; B) b1) engine-oil c1) engine-sensors; and so on). Furthermore, those are not static values I could pick up from a pre-defined list, I need to enter values by hand.

2. "a table for each Repair and a table for each Report", does this mean that you are generating Tables to hold report data? This is not advisable, as reporting data should be collected from the tables already in place using Queries.

We usually write a report of the work done and maybe some advice/notes on things we've noticed during the work, that is what I intended to store on a different table as Reports. but I guess I could store this in an additional field within the RepairX tables if it is better this way. Would you recommend that?

3. You appear to have forgotten one of the most important elements in the Process by focusing on the "Car", which is the Customer. Especially as the Customer may have more than one car that you work on

.
The "Car" table does have field for the owner's details but, truth is, around here people rarely has more than one car... anyway, that is is a good point, note taken!

Regards.

[hqnet]

Originally Posted by midders

You're duplicating data; CarID is only needed in the Car and Visit tables. You only need two, one to many, links:
tblCar.CarID = tblVisit.CarID
tblVisit.VisitID = tblRepair.VisitID

Assuming you mean "tblVisit.VisitID = tblRepair.VisitID" should be done for each tblRepair (1 to 5) making for a total of 6 lines, that is what I already have.

I had started linking with CarID but then limited that to tblCar and tblVisit, the fields still remain in the other tables but not used, just untill I clean the project a bit.

I tinkered some more but I'm still stuck in the same place: when I focus on a RepairX form it doesn't have the proper VisitID, it does have its autonumbered RepairID and at best (depending on what I try) it has the proper CarID (which I am not using anymore)...
I _feel_ like the problem is in one of these places:

- There is something wrong with the way Key IDs or indexed fields are set.

or

- my relations are still wrong ...
...maybe there is some detail about those 2 relations I am unaware of? Something in the "Combination Modes" or the "cascade updating fields"??
(I'm not using an english version, pls excuse the bad translation)

Thanks again.

[OBP]

hqnet, I beg to differ about the Table Structure, there should be a Repair Category Table which holds your "A) a1) front breaks a2) rear breakes a3) hand break; B) b1) engine-oil c1) engine-sensors; and so on)" which are then selectable in a Combo box and a separate table for the Repair Details. The table would then hold the CarID, the RepairID from the table that I proposed and then the actual repair details, like the repair description, repair date (repair cost if required) etc.

[OBP]

Ok to explain what I am talking about I have created a simple Car Visit/Repair database for you to look at.
Obviously it could include Customer Details, Parts used, Parts Inventory, Time taken for repairs, Repair Charges etc

[hqnet]

OBP, thank you so much for taking the time to setting up that example, I really appreciate it!

I´m looking into it right now and I think what I am doing is not so far away from it, but I did found some differences that could be the reason of my problems and will work on that today.

I beg to differ about the Table Structure

Please, by all means, you obviously know more than I do

FWIW, If I translate my error to your example, when I enter a value in the "Car Visits form" and CarVisitID is created, that value is not propagated to VisitID in "Visit repairs form".

I will post back after I move the project closer to your example.

Thanks again.

[hqnet]

I finally made some progress thanks to your example

It seems my mistake was that I had placed the 'tblRepairX forms' at the same nest level than 'tblVisit form' instead of nesting it inside 'tblVisit form' . I was expecting that was merely a layout matter.

I made a 4 fields draft of the new forms using the same tables and relations (well, I was able to properly tune them now) and it seems to be on the right track now.

I will keep working further and post back.

Thank you!!
Cheers!

[OBP]

I do hope that you are not still using 5 tables for Repairs.

[hqnet]

Hi,
something came up and I didn´t have time to work on this for a few of days.

This is a breakdown of my originally intended Repairs tables, should I really cram these toghether? I realize it would make queries a lot easier, but I fear it will become a monster when handling 500 records... Then again, I don´t know enough yet

Rep1 - 30 fields
- 19 with manually enter values and text
- 4 with parameters picked from parameters table
- 7 auto-calculated based on params and input data

Rep2 - 24 fields
- 19 with manually enter values and text
- 0 with parameters picked from parameters table
- 5 auto-calculated based on input data

Rep3 - 13 fields
- 13 with 1 to 5 values picked from checkboxes

Rep4 - 8 fields
- 8 with 1 to 5 values picked from checkboxes

Rep5 -
not yet defined but will be along the lines of Rep2 I think.

Rep1 will be used almost at every Visit, but the others will be used only 25% of the time, which is actually why I split them in different tables.

Best regards.

[OBP]

If the other tables have the same Fields as Report 1 then you can have one table with 5 Forms to input the data. You would then have 5 Queries and 5 Reports.

It will 1000s of records easily.

[hqnet]

Originally Posted by OBP

If the other tables have the same Fields as Report 1 then you can have one table with 5 Forms to input the data. You would then have 5 Queries and 5 Reports.
It will 1000s of records easily.

I´m sorry, I don´t understand what you mean with "have the same Fields as Report 1" (I presume you refer to my "Rep"s) but each field in each table represents a different concept/item (other than the obvious ones such as date, et. al).

I am sure you already imagine that and you were talking about someting else that I could´t grasp...?

Thanks

[OBP]

No I don't think so, normally reports are about the same basic things, just about different Vehicle Systems (I worked for Fords for 33 years, so I know about vehicle systems).
In most cases you have common fields, like Owner name, VehicleID, System Name, Visit/repair date, etc.
The other fields would normally be in Sub Table based on the System, without seeing you Relationships I can't tell if your tables should be normallised or not.

[hqnet]

Ok, but my concern is with the Repairs tables not the Reports one...

>> The other fields would normally be in Sub Table based on the System

...that Sub Table is what I broke down in 5 because I felt having a single table with ~75 columns would be problematic (and many of them would be stored empty most of the time anyway).

I guess you think this the wrong approach and I should use just a single table for Repairs and di the breakdown at the forms and reports level?

Thanks