Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2005, 02:25 AM   #1
Bill Harrison
Registered User
 
Join Date: Jan 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Bill Harrison is on a distinguished road
Help with Relationships and table design:

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 is offline   Reply With Quote
Old 01-11-2005, 02:33 AM   #2
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Quote:
Originally Posted by Bill Harrison
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.
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.
Mile-O is offline   Reply With Quote
Old 01-11-2005, 05:02 AM   #3
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
Quote:
Originally Posted by SJ McAbney
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.
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.

__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 01-11-2005, 05:06 AM   #4
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Quote:
Originally Posted by neileg
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.
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.
Mile-O is offline   Reply With Quote
Old 01-11-2005, 05:47 AM   #5
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
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!
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 01-11-2005, 09:11 AM   #6
Bill Harrison
Registered User
 
Join Date: Jan 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Bill Harrison is on a distinguished road
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?
Bill Harrison is offline   Reply With Quote
Old 01-12-2005, 01:42 AM   #7
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
=[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.

__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 01-12-2005, 06:44 AM   #8
Bill Harrison
Registered User
 
Join Date: Jan 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Bill Harrison is on a distinguished road
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

Bill Harrison is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 07:48 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World