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!