Help with Relationships and table design: (1 Viewer)

Hi all! Here is what I want to do, and what I have done so far:

I have one table called "AUTOS" This is a table imported from pc file, and tweaked to make it work well in Access. I learned alot doing this, and now have workable forms, queries, and reports being generated on this table. I learned on here not to let my users "work" with the table, only through forms. So that all works very nicely!

Here is the next step. First a simple background: We are a Used car dealer. Table "AUTOS" contains rows, each one consisting of many Fields. The main one being "Stock NO". Then all the remaining fields are vehicle info (many fields, but all relating to the vehicle type etc), cost, sales price, etc.

What I want to do is create a new table, called RECONDITIONING. In this table I want to store everything done to a vehicle after purchase. For example, if we purchase a vehicle, then change the oil, put on new tires, and detail it, I want these items in the reconditioning table. But I want them to remain linked to the "correct" stock number in the main table.

I believe I have done this, by doing this: I created a second table called reconditioning. Added the fields I wanted, the first one being "Stock NO". I made "Stock NO" in the main table autos the primary key, and then opened relationships, and linked the reconditioning table to the main table via that field.

It appears to have done what I wanted too, because if I open the reconditioning table, and type in a vehicles stock no, then the reconditioning info, then go back to the main table, there is now a + next to the row. If I click this plus, a box drops down with each item I added to the reconditioning table. So far so good!

Here is the main question: Now that I have this info in what seems like the proper fashion, how do I work with it?

I need to create a form, containing elements from EACH table. The design I have in mind is like this:

Across the top, I want vehicle info:

Stock No Year Make Model Date In Cost
5922 1994 Ford Focus 1/1/05 1000.00

Then under that, I want a "table" in a form, which pulls from my "reconditioning" table, listing each item that has a stock No that matches the one on that record:

5922 Replaced Windsheild Wipers 1/8/05 20.00
5922 Tuned Up 1/10/05 75.00


Ok, now, I want to allow my users to add to that on this form, to type in new entrys. I think I got the idea how to do that, but suggestions are welcome. Now, on this form, I want at the bottom, the following:

Vehicle Notes: (Just a text field for "notes" on a particular vehicle)

Total Reconditioning cost: (this is a field on the FIRST table, AUTOS, that I want to calculate by adding the total from the list above, IE in this case, 95.00)

Total_ACV: This is a field from the first table as well, which I want to calculate using (Vehicle cost: Which is in the first table)+(Total Reconditioning Cost: which is above)

Like I said, I think I have the table layout and design correct, I am just not sure of how to work with values in linked tables like I have done, and I am not sure get the right info in my list on the form, then add the totals properly.

Any suggestions are appreciated!

Bill

Bill Harrison said:

I believe I have done this, by doing this: I created a second table called reconditioning. Added the fields I wanted, the first one being "Stock NO". I made "Stock NO" in the main table autos the primary key, and then opened relationships, and linked the reconditioning table to the main table via that field.

Click to expand...

No, you have not done this properly at all. This new table reads like a repeatable group (i.e. you have a field for each possible operation that can be done) which is an indication that you need a new table to cover these.

And I'd rename the tables to tblAutos so as to disambiguate them.

Okay, you have your autos table
I suggest a table for operations:

tblOperations
OperationID
Operation

tblAutosToOperations
Stock No
OperationID

This second table is a junction table as this is a many-to-many relationship which Access can't directly support.

With this you can now add a subform to your form.

I'd also wager that you have make and model in the autos table - these belong in separate tables too.

SJ McAbney said:

No, you have not done this properly at all. This new table reads like a repeatable group (i.e. you have a field for each possible operation that can be done) which is an indication that you need a new table to cover these.

Click to expand...

Sorry, SJ McA, I don't think so. Sounds OK to me. I don't think this is a many to many, either. Since the operations that are done in reconditioning a car are so varied, I imagine that this is a simple one to many relationship.

Bill, I think your best bet is to work with a form/sub form. Hold the car details in the form and add a sub form that shows the corresponding entries from the reconditioning table. Access will manage the parent/child relationship for you.

neileg said:

I don't think this is a many to many, either. Since the operations that are done in reconditioning a car are so varied, I imagine that this is a simple one to many relationship.

Click to expand...

I'm just the sort of person who likes to have everything categorised so that I can play around with numbers of anything. For example, with what's been done to the car I'd rather have a combobox listing loads of possible things than a freetype situation. Just my preference.

Yes, I understand. Trouble is I think there's an almost infinite range of opeartions that sould be carried out.

There are a couple of standardised lists of repair types defined by UK motor industry bodies, but I don't know any vehicle workshop that actually uses them.

Still, as you say, it's personal preference.

Bill, it's your choice!

Thanks guys, got it up and halfway working, subform now shows reconditioning done per stock number.

Now, how can I get the subtotal of the "reconditioning" from the subform TO the main form to calculate my ACV? I have tried using the example in ACCESS help, but it keeps showing me #ERROR in the text box!

ACK!

Here is what I have in the text box:

=[Reconditioning subform].Form!Text8

But I keep getting #ERROR on the form

??? Any suggestions?

Also, how do I calculate total ACV based on that total, totalacv being a field on the main form?

=[totalacv]+[Forms].[Reconditioning subform].[Text8]

This will give you the total if you place this in a text box in the main form. You don't need to carry the total from the subform to a text box in the main form, using the reference to the box on the subform is enough.

You may find you get problems when either of the two values is null, for example when you have no reconditioning done. To avoid this, use the null to zero function Nz()
=Nz([totalacv])+Nz([Forms].[Reconditioning subform].[Text8])

By the way, it's a good idea to adopt a decent naming convention that avoids the use of spaces and punctuation for your database objects. So instead of [Reconditioning subform], use subfrmReconditioning. Instead of Text8 use txtReconTotal, or something along those lines. Also, avoid using reserved words like Name or Date as object or field names.

Thanks for the great tips Neil, only one last hurtle! I now have the value showing up properly in a text box on the main form. All I need to do now is store this value in the field "totalacv" PERMANENTLY, anytime a new "reconditioning" item is added to the subform. I can't seem to get this to work right!

Thanks