Creating relationships in tables

[Vibes]

I am very new to Access.




I have created dummy tables reflecting shopping by a few people (please see attachment).


I am not able to get my head around how to generate a report showing how much each person spent on each fruit and what was the total amount spent by each person. Can someone please help.

[CJ_London]

Your purchase table is not normalised

you should have something like

tblCustomers
CustomerPK autonumber
CustomerName text

tblFruit
FruitPK autonumber
FruitName text
FruitCost currency

tblPurchases
PurchasePK autonumber
CustomerFK long - link to customerPK
FruitFK long - link to FruitPK
Weight double
FruitCost currency

You'll note that fruit cost is there twice, the reason is that costs will vary over time, so you need to store the cost at the time of sale.

Done this way, it is easy to see who spent how much and on what fruit.

Your way will require a messy query which will need to be changed every time you add a new fruit

[orange]

As Ajax said, spend time to learn and understand Normalization. Make a list of the "things" involved in your business, and how they relate to each other. Build a model, test it, adjust it until it meets your requirements--- then start with Access.
See videos 1,2 and 4 in the Dr. Soper series starting here.

Don't be too quick to jump into Access. Understand database concepts first.

[Vibes]

@Ajax and Orange: many thanks for guiding me to a great online learning resouce.


With your inputs, I have been able to fix my tables and get my database to do what I wanted.


However now I am conidering a case where the customers visit different shops which have different


pricelists. I have a feeling that there need to be a colum in the table shopping and then have a


calculated field accordingly but have not been able to actually make it work.


How do i incorporate the different pricelists. I have tried differnt things and also looked for guidance on abailable online resources but have not been bale to find anything thus far.


Can you please be kind enough one more time and guide me on this? I am attaching the excel file


from where I am importing data.


Regards
Vibes