I have two tables : Customer and Purchase. I have a query that connects these two tables and identifies what a customer purchases. Also a customer can make more than one purchase, each purchase is a new activity - or row in the table.
The Customer table has the following fields: Last Name, First Name, Unique Customer ID.
The Purchase table hast the following fields: Unique Purchase ID, Purchase Date, Payment Type, Purchase From, Ship To, and Unique Customer ID (looked up from the Customer table).
The Customer Purchase query combines all the data from the two tables and is joined where fields from both tables are equal. The tables and queries work fine. For the Customer table I can see all the customers that exist. For the Purchase table I can see all the purchases that have been made them (based on the unique purchase ID) and who made them (based on the unique customer ID). For the query I can see all the purchase information in addition to the actual names of the customers. There are several customers that have made multiple purchases and I can view each one in the table – as each as a unique purchase ID.
The problem I have experienced is with the form. I want to view those customers that have made purchases. Instead of utilizing the Customer ID from the field list and selected – find a record on my form based on the value I selected in my combo box- I then selected Customer ID from the Customer Purchase query.
In the form view of the form I can see all the Customer ID’s in the combo box. Those customers with more than one purchase have their Customer ID listed more than once. However, for those customers, the form is only populated with the purchase information from one purchase. I can select a different instance but the purchase information does not change. I’m not sure why this is happening.
Help is greatly appreciated.