Used Car Inventory, customer, and payments database problems 1

[doommarine44]

I am running a small used car lot doing buy here pay here, and im trying to setup a database accordingly.. but i cant quite get it going right.. heres an explanation of how i have it setup.

I first have an inventory table with the stock ID being the primary key, it is auto numbered. the rest of the table has car info such as make model etc.

The main table is customer info, it has customer ID as primary on autonumber, and also has stock number as a number on it, it also contains there name address etc.

Last i have a payments table, it has the last date paid and next date due and amount. It has the Customer ID and the Stock ID in it..

I would like to have each car have a customer that is buying it, and then each customers has payments they are paying.. but im not really sure how to tie it all together and make it work..

The main problem is, I have an inventory form, to easily fill out all the info, then i have a command button that opens customer info, but.. my prob is that whenever i hit the customer info butotn to fill it out, it doesnt automatically relate the new customer to the current car i was viewing in the inventory form, so after i fill it all out it and close then reopen the customer info menu via the cmd button that it is all blank but the records stored, I guess i want a way to automactically make each new customer be related to the current car stock id open in the inventory form..

or is there a more effiecent way to setup my tables, but i still would like to have each car tied to a customer and payments tied to each customer.. but with very functional forms..

I still havent found a way to automatically carry the stock ID of the car I am viewing in on form, to the Stock ID in the customer info form, but lets say I have stock id 22 and its a nissan sentra, and i want to tie it to joe jones, i can surely open the customer info table and change the stock id to 22, then all the forms show up right, but... is there a way to automatically do this? I have the relationship setup what i think should be correct, is there any way that access could be bugged? Or could someone give me a sample relationship setup that would work if mine is flawed? thanks !!

on my relationships I have stock id in my main table inventory, tied to stock id in my 2nd table, customer info, then i have customer info in customer info tied to customer info in the payments table.. is this the way the relationships should be setup to do what i would like, which again is automatically carrying the stock id from the current car i am viewing in my inventory, over to the stock id of the customer info form(and table), I open the customer info form via clicking a command button on the inventory form. (just to let you know)

I hope this gives you a better idea of my situation. thanks for any help, this is about to kill me..


Heres a link to my database, please look it over and shoot me some ideas, or even mod it and upload your copy. thanks alot!!

http://home.alltel.net/deckofcars/carpricerforupload.mdb

 

[willir]

DoomMarine44

First, have you read some of the following background info??

Fundamentals of Relational Database Design
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

...Moving on

As I understand it, you want your Used cars database to...
- track stock (cars, parts, both??)
- customer
- payments
- ownership
- automate some process

I may suggest that you also track suppliers, especially if you are managing parts.

I have run home and do the parent thing, but I will post back later some moreinfo.

Richard

 

[doommarine44]

I have read some on design, just alot of the realtionship stuff has been somewhat vague, I think i have a decent setup, just i can't get one tiny thing to work.. and its ultra annoying. It seems the way i have it setup the stock ID should carry over but it doesnt if i add a new record, it does if i add the stock id to each customer manually and all the relationships work right then.. its just adding a new customer.. it just doesnt carry from one form to another, like i would assume it should.

I don't really know about the parts, i just really need the cars customers and their payments setup in a working order. Thanks again for any help.!

 

[PHV]

I think i have a decent setup
What about a customer buying 2 cars ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[doommarine44]

I think my current setup will allow more than one car per customer, however I am new to access somewhat.. So I not 100% sure of this, if someone could check out my database i posted and, use the inventory form then click customer info and try to add a new record for that car.. you will see what i am talking about..

 

[PHV]

I think my current setup will allow more than one car per customer
Yes, but you have to enter there name address etc for each car.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[doommarine44]

Yes i havent thought about that to much PHV, I will have to re enter date, however that is non-important atm as I am just getting started, and and in dire need of a functional database that i can use to print out all the sale forms, ready to go. Any suggestions on how to make it functional?

 

[doommarine44]

Oh and not the forms part, I just need help with my stockID issue, or some way of setting up the relationships in an effcient manner, to allow for that one automated step, carrying the StockID over to the other form, upon creation of a new customer record, see my upper posts to see what I am having issues with. thanks

 

[PHV]

You may take a look at the Northwind database for good ideas.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

DoomMarine44

Okay, I am back. This is what I came up with...

tblCustomer
CustomerID - primary key
CustomerLN - last name
CustomerFN - first name
CosignerID - foreign key
PhoneNo - text
Address
...etc

Discussion:
This is a fairly typical contact table except that it allows you to point to a co-signer (another customer)


tblManufacture
ManufCode - primary key
Manufacture

tblModel
ModelCode - primary key
ManufCode - foreign key to tblManufacture
ModelName
ModelYear
Odometer - long interger
...etc

Discussion:
You may feel tracking manufacture and model as unnecessary. Perhaps. It is very little work, and would allow you to have an idea on how many of which cars were sold, and look at historic trends. Nothing beats experience, but this information may help you make some future marketing decisions.


tblAuto
AutoID - primary key
ModelCode - foreign key to tblModel
VIN - text
PurchasePrice - currency
CurrentOwnerID - foreign key to tblCustomer
...etc


tblContract
ContractID - primary ID
CustomerID - foreign key to tblCustomer
ContractDate - date field
SalePrice - currency
...etc

Now tracking payments can be tricky. Ideally, you should use a double credit and debit entry system - better tracking, better reporting. However, I suspect you are looking for more of a down-and-dirty approach.

tblTrans
TransID - primary key
TransDate - date field
PayeeID - foreign key to tblCustomer
TransCode - text
TransAmount - currency
Reference - text
Comment - memo field

Discussion:
You will have a one-to-many relationship between the contract and the transactions. You will have credit and debit entires, and when payments have been completed, the net should be zero. TransCode or transaction code will determine the type of transaction...

The reference field can be used to track financial info such as cheque number.

[tt]
tblTrans
TransID TransDate PayeeID TransCode TransAmount
1 01/10/2004 1 Purchase 20,000.00
2 01/10/2004 1 DownPay -5,000.00
3 02/01/2004 1 MonthlyPay -1,500.00
4 02/01/2004 1 Interest 62.50
5 03/01/2004 1 MonthlyPay -1,500.00
6 03/01/2004 1 Interest 56.51
7 04/01/2004 1 MonthlyPay -1,500.00
8 04/01/2004 1 Interest 50.50
9 05/01/2004 1 MonthlyPay -1,500.00
10 05/01/2004 1 Interest 44.46
11 06/01/2004 1 MonthlyPay -1,500.00
12 06/01/2004 1 Interest 38.39
[/tt]
At the end of 6 months, customer / payee 1 has...
Made
7,500 in monthly payments
5,000 down payment
And accummulated
252.36 in interest payments
For a net amount owing on the original purchase of
7,752.36


Now, how to make the transactions...
You can enter the transactions into a subform. But ideally, money transactions should be tightly controled with the use visual basic coding to commit the transactions may be better. Post back on this later if you want -- I have some code that seems to work well for this type of thing.

So, what is next...
- You have a design
- You have a good idea on your expectations

The next step is to enter some test data and make sure you can retrieve the information you are looking for. Some tweaks may be required. And this is when you want to make sure your design is correct -- much easier to fix problems now than after your systme is in production and then you discover a serious design problem.

Richard

 

[ZmrAbdulla]

I have given a starting with a sample db. It is simple to start. He might need many things to add.
PHV's Question about customer buying 2 cars, is not a problem in this. Each sales is separate.
thread669-990101

Zameer Abdulla
Visit Me