| Author |
Topic |
|
Sweet16
Starting Member
15 Posts |
 Posted - 2011-09-15 : 15:32:56
|
| I have two tables :Customer and Purchase. I have a query that connects these two tables that identifies what a customer purchases. Also a customer can make more than one purchase, each purchase is a nw activity - or row in the table. I can view all the purchases in table however, for the related form I can only veiw one of the purchases a customer has made, even if they have made multiples. I have a combo box which lets me select the cusomter- their name will appear multiple times but the related purcahse infomation will be the same for each instance. Is there any way to fix this? Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-16 : 12:03:01
|
| sounds like you problem is join on wrong way. As suggested post some sample data from table with output and we will help you to fix it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Sweet16
Starting Member
15 Posts |
Posted - 2011-09-19 : 13:24:50
|
| The two tables I have are Customer and Purchase. 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-19 : 13:29:42
|
| how is the query written currently to retrieve the purchase information for a customer based on selection from screen?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sweet16
Starting Member
15 Posts |
Posted - 2011-09-19 : 13:47:42
|
| SELECT tbl_Purchase.PurchaseID, tbl_Customer.CustomerLastName, tbl_Customer.CustomerFirstName, tbl_Purchase.CustomerID, tbl_Purchase.PaymentInfo, tbl_Purchase.PurchaseFrom, tbl_Purchase.ShipTo, tblPurchase.PurchaseDateFROM tbl_Customer INNER JOIN tbl_Purchase ON tbl_Customer.CustomerID=tbl_Purchase.CustomerID ORDER BY tbl_Customer.CustomerLastName, tbl_Purchase.CustomerID; |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-19 : 13:52:03
|
| where are you doing filter on customer id? this just shows all customers with all their purchases. whats the query executed when you click on customer to view their purchase?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sweet16
Starting Member
15 Posts |
Posted - 2011-09-19 : 13:59:24
|
| I don't have a query to do that. Is this what I am missing? I thought that if I created a query to link the cutomers and their purchass I would be able to view them in a form where I could use a combo box to select the specific instance I was looking for. I can see what I need in the query I'm not sure why I can't see that same information in the form. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-19 : 14:06:35
|
| ok if you dont have a separate query how are you performing the drillthrough action to get associated purchases?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sweet16
Starting Member
15 Posts |
Posted - 2011-09-19 : 14:16:53
|
| I'm not sure what you mean, sorry. I'm very new at access so I'm still learning. Based on the Purchase table, every purchase has a unique purchase ID and every purchase has an associated customer ID. The query I have just combines the purchase information with the customer name. I can see all the purchases, including the multiples, in the query table. Though, with the the combo box I have on the form, I can scroll though all the entries based on the customer ID. Howevever, if the same Customer ID is listed more than once the purchase information will only be displayed for one of them and will not change regardless of which instance I select. Perhaps the 'drillthrough' query is what I am missing. I'm new to access so I'm not sure what this menas or how to do it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-19 : 14:26:17
|
| i'm not sure on how access handles events so you may be better off posting this in Access forum.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sweet16
Starting Member
15 Posts |
Posted - 2011-09-19 : 15:55:40
|
| Ok. Thanks for the guidance. |
|
|
|
|
Multioccurence Form