How would i setup these relationships..? 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..

 

[ZmrAbdulla]

How are you doommarine44 ?
First of all...
Can you tell us what Database you are using? As this is Genral Database Discussion Forum it is difficult to assume..
If it is Access Database then here is some Sample Databases may help you to a start.
See fundamentals of Database Design here
Regards

Zameer Abdulla
Visit Me

 

[doommarine44]

oops i forgot,the database is access. This problem is simple but its hard, it seems like it should work with just relationships.. but its not.

 

[doommarine44]

I have looked at many of those on that site already, and read most of those tutorials, but 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 !!

 

[doommarine44]

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..

 

[ZmrAbdulla]

I will try to explain how exactly to create tables for your app in this evening. Right now I am little busy with some other work
Regards

Zameer Abdulla
Visit Me

 

[ZmrAbdulla]

I have created a Sample file (Zip 32 kb) to show how to start.

BTW After this Please post your questions in the appropriate forums. You will get more response from there
Microsoft: Access Forms Forum
Microsoft: Access Modules (VBA Coding) Forum
Microsoft: Access Queries and JET SQL Forum
Microsoft: Access Reports Forum
Microsoft: Access Tables and Relationships Forum
Microsoft: Access Other topics Forum
Regards

Zameer Abdulla
Visit Me

 

[ceh4702]

I would have thought you would have a contract or an agreement form for each sale. On the contract it would link the customer up to the automobile say when the first payment starts, what the payment amount is and how many total payments there will be. Eventually someone comes to buy a second car and that will cause a 1 to many or a many to many relationship. I dont know if 2 people can buy a car or how that works if you have a co-owner.

Also you probably want one table just for payments. You need a historical record of each payment. You need to be able to look back and determine if a back payment was paid or it was received late or whatever.

If you do not like my post feel free to point out your opinion or my errors.

 

[doommarine44]

Mr Abdulla, thanks for the help, I will look the database over, and did you use any programming or is this all done by point and click?? I have my data base uploaded

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

theres a link for it please look it over if you have time, and perhaps see if you can figure out my problem, or just suggest a new table or way to set it up!! also I didnt know which form to post my question in really, but I have posted it to relationships now, but any way..

Thanks for the help, and sample database, I like many of the ideas in it and will try to implement them into mine sooner or later!