design using relationship...

[dsnyder]

I have a small sheet music publishing company and I’m trying to create a system to do the following:
1.Product info (I’ve created a table holding this info)
2.Customer info (I’ve created a table holding this info)
3.Keep track of product purchases
Who bought it, when, for how much
4.Calculate the total sales of each product item



DB stuff is totally new to me and I’m not sure how to connect the info from 1 & 2 into 3 – or the best overall design if the DB for that matter…Obviously, I’d like to not reenter customer info for each item they buy and I’d like to be able to query things like who has bought a certain item and when.

I have learned some basics on ‘relationships’ and used that for common fields in my Products table
Thanks for any guidance….

[dsnyder]

i've created a flowchart of sorts to outline the charts that I think are needed. As this is my first db, any comments on what you see would be appreciated...ie. how to uses 'relationships' more, other things that I should read up on...don't really know what I'm doing

[Matrix]

The 'Orders' table would be like this:

OrderID
CustomerID
SheetMusicID
PurchaseDetails
PurchasePrice
ShippingPrice
... 

So the three tables 'SheetMusic', 'Customer' and 'Orders' are connected. You can make different queries on 'Orders'. e.g.:
List all orders of a title:

select a.* from Orders a, SheetMusic b where a.SheetMusicID=b.SheetMusicID and b.Title='my favorite song' 

(Just a fake SQL)

Just some thought on database design, I have no Access coding experience, although I worked a lot on other databases.