Access 2010 Help

[B&W]

Hi need to design a database for work, not very good at it so need some help.

It's a fairly simple database with just one table needed at the moment. It involves customers loaning an item (which has 3 variants in sizes) and sometimes they will return them so I need two columns (one for items returned, one for items remaining)

After the usual columns such as address/name ill have:

Bottle 1|Bottle 2|Bottle 3|Deposit Paid|Bottles Returned|Bottles Remaining|


So if a customer loans 2 of each then returns 1 x bottle1 and 1 x bottle2 I want to know the amounts returned and amounts remaining.

It would be good if I knew how much of each type he returned and how much remains.

How would I do this in access, would I need multiple tables?

 

[izzop]

Hi,

Just had a quick read and haven't spent a great deal of time thinking about it, but to answer your question - Yes.

I would have a Customers table, Items table and then a Transactions/Loans table.

 

[B&W]

Done that, now how do I make a relationship with it so when the customer line is filled in on the customers tables it matche the other line on the items tables.

 

[izzop]

Hi,

Very quick and dirty, but hopefully it'll give you an idea.

Sample Database

Regards

 

[B&W]

Thank you, looking at the relationships trying to make some sense of it.

 

[izzop]

Thank you, looking at the relationships trying to make some sense of it.

You're welcome.

If you also open the Orders Table you'll be able to add orders and then click on the plus sign you can enter the bottles that have gone out against that order.

It might help you understand it more, who knows it might do what you require?

Regards

 

[B&W]

It's been 10 years since I last made a access database, now im married and got a kid.

Makes everything 10 times harder to understand lol.

It looks like it does most things, im trying to remember what primary keys and foreign keys are all about.