I'm very new to databse design and I need some help with a calculation that i'm working on

i have 4 tables. customer, product, line item, and category

The line item table is connected to the product, customer tables and the product table is connected to the category table and contains the pricing information

i can get the sum of all the purchases made by a single customer but i am not sure how to get the sum of the purchases in a single category for each customer.

any help on this is greatly appreciated

thanks

You need a relationship like above. Create a global field to choose the category that is to be displayed.

-jens

Edited October 27, 200619 yr by Guest

Hi. thanks for your help but i'm afraid i still don't understand how to set this up.

I've read through several topics on global fields but i am not sure where to place certain items.

i want to select the category within the service table so it can be repeatedly used without having to select the category time and again. I want the service to be selected in the line item which also selects the customer id to create the relationship.

I dont understand what the global field should be and which fields it should be related to.

and finally, how can i create an equation to calculate the sum of the line items of each category for an individual patient between a start date and end date.

this is what my er diagram is so far

thanks again