Tracking Purchase History

[RPrechelt]

Hey Everybody,

I'm relatively new to Access and im still learning about all its tricks and nuances. I was wondering if anyone can help me with a small problem I'm having. It's forms and tables. Im trying to track independent purchase histories for customers (test system) and i want to display these purchase histories in the output form. I have a purchase history's table which stores the ID (Primary Key) of the purchaser; and it is constantly updated with more purchase's. Excuse the basic question, but how i would i display the purchase histories without manually updating them? Im basically trying to display a 'data-sheet view' within a field of a form. Perhaps a query within a field? And is there anyway to have each order on a separate line within a field?

Thanks for all the Help

 

[HiTechCoach]

For us to be able to help you, we will need to know your table structures.

If possible, can you post a sample database with your table and some sample data. Be sure to remove any confidential data. Be sure to include some sample data in the tables.

 

[RPrechelt]

Unfortunately, the database is still in the midst of being created; it wont be ready for about a week...

there is a member table which contains data about the members (a unique Primary ID is generated for each member). WHen a sale is made, the purchase items (each product has a unique ID) are stored in a database. Each product record stores the Products ID and the Member (who brought the items) ID.

 

[missinglinq]

For this kind of thing you'd typically have three tables, one for Customers, one for Orders and one for Products Typical, simplified table structures would be something like this:

CustomerTable

CustomerID (PK)
CustomerAddress
CustomerPhone

OrderTable

CustomerID (FK)
OrderID (PK)
OrderDate

ProductTable

OrderID (FK)
ProductID (PK)
ProductCost

with the relationship being

Customer (one) to Orders (many)

and

Orders (one) to Products (many)

Notice that the Primary Key (PK) from one table becomes the Foreign Key (FK) for the next table down the line.

Form-wise, this would normally be displayed as

Main form (Customers)
SubForm1 (Orders-subform to Main form)
SubForm2 (Products-subform to Subform1)

with the Parent/Child links being PK to FK for each form.

 

[gemma-the-husky]

Unfortunately, the database is still in the midst of being created; it wont be ready for about a week...

there is a member table which contains data about the members (a unique Primary ID is generated for each member). WHen a sale is made, the purchase items (each product has a unique ID) are stored in a database. Each product record stores the Products ID and the Member (who brought the items) ID.

When designing databases, you should consider all the data elements you need to store in the database, and their logical arrangement at the beginning of the project. (these will be partly suggested by the reports and forms you need to see in respect of that data, and any specific functionality you need) This is a large part of a fundamental process called normalization, which is part of the system design and analysis phase

wth a properly normalized database, the solution to most problems can be readily seen, and the results fairly easily achieved,


Now your problem may just be due to inexperience and unfamiliarity, but it may also be due to a poor database design

The point is that it is wrong to be saying the database is being created .....

if the problem that you want to resolve is not met by the current database design, then the database needs to be redesigned. I am not saying that it can't be done, but that it should be fairly trivial to do, and it should also be apparent how you intend to do it, ie considering things like this should be part of the system design phase. (should already have been done)

You will find that if the data is not structured correctly (normalised) then things that should be easy will become hard - and your data may end up being inconsistent and conflicting, (ie different parts of the database may show different values for what should be the same) - and fixing it will be very difficult.