tables and relationships

[pmol123]

Hi,

I have an (quite old) Perfect view database for someone who has got a (bike)shop and would like to have it migrated to Access, I am just not sure how to setup the tables (which ones) and which relation ships should be created.

at the link below you can see which tables were created in Perfect view:
http://members.chello.nl/~p.mol15/access.jpg

you can see that the address information is used several times in some tables, I guess this should be merged into 1 or 2 tables.

I think the situation is quite similar to other shops, who have parts, customers and create bill's, so I hope someone can help me a hand how I could setup these tables and relations in access.

one thing I am not sure of is that I need to keep the bill's which are created on a specific date which contains customer information (street and zip code)and shouldn't change when this customer moves to another place, so this information has to be saved in the table which has the billing information in it.

any suggestions are more than welcome.

thank you very much.

 

[pmol]

Hi,

If you use windows XP you can click on the image and in the lower right corner of the image an icon will appear, if you click on this icon you can see the real size of the image which is very clear.

Hopefully you can give me some tips.

thanks.

I can't read the tables and fields in the image because of its poor quality. I do see a repeating group of some kind. I can't make out the field name but the same name is repeated a number of times with a numeric suffix.

 

[pmol]

Hi,

Firstable these tables have been converted from Perfect View databases, and I think that the person who made it could have done it a lot better. but I'll try to explain it.
Those PR1-PR15 fields have been made to create a bill and put parts on it which are being sold,
so if I am a customer and want to buy 5 parts they are being entered in Aantal 1-5 and Pr1-5 are the fields where the price of the parts will be entered. I was wondering if there is another way to do this.

For the rest, I want to make a new database which only contains all thing necessary, so if you imagine a (small) shop which sells bikes and parts I need to setup a correct database for this.

I have to deal with :

Customer information
Parts which are being sold
bill's which have to be created
second hand bikes which will be returned
new bikes being sold.
suppliers of bikes and parts.

Could you tell me what kind of setup of tables could be used for this ? and which relations I should create ?

thanks for your help.

 

[pmol]

Ok,

I will have a look at "Orders", but do you know a sollution for the problem when I create a form to make a bill and where components can be added that I don't know how many components will be added, so I don't know how many fields I have to create in that table.

Thanks