General discussion


Access -Restricting the query to one raw

By george123 ·
I am an average (maybe, under average) user of Access ? not a developer. A friend owing a small corner street shop asked me to help him organize the information about his customers.
I created a database composed of two tables (and their corresponding forms):
-?Customers? (containing the main data of each customer, like: Name, address, age, bank account,etc.)
-?Daily_purchases? -listing all the purchases made by all customers (containing the Name of each customer, date, type of bought products , value, aso).
I established the relationship between the Name of customers in both tables as: "one-to-many" ("one" being the Name in the Customers table). I also created a Select Query to retrieve the Name of a customer and all his personal data from the first table ("Customers") as well as all the purchases he made (collected from the second, ?Daily_purchases? table). I saved this query as a make-table query.
My problem is the following:
the query retrieves all the names and all the purchases from the database.
What I would like to have is a kind of filtering: like, when I select a customer in a form to get only the information from the query pertaining only to this specific customer; further, based on this information, the user will able ?if needed- to make/print a report.
I tried something with a Macro starting the query but I cannot restrict the search to only one customer. Also maybe a parameter query might work but I would like the user to be able to introduce the parameter (like: Name) from a form not from the Query in Design View.
For a seasoned Access or SQL programmer this is probably a very simple problem, but at my level I cannot solve it.
I will appreciate if someone can give me a hand or suggest a more elegant solution.
Thanks a lot in advance!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by JRod86 In reply to Access -Restricting the q ...

This should be fairly easy. If you have the customer name in the Daily_purchases table, you could do something like SELECT * from Daily_purchases WHERE Customer_Name = [whatever your dropdown box name is from the form].

If you want information from the customer table, you would have to do an Inner Join. Something like: SELECT Customer.Customer_Name,, Daily_purchases.type FROM Customer INNER JOIN Daily_purchases ON Customer.Customer_Name = Daily_Purchases.Customer_Name WHERE Cutomer.Customer_Name = [whatever your dropdown box is named from the form].

I would also look into normalizing your database. Google database normalization so that in the future, if you take on larger projects, you will be much more successful and the data in the database will be safer.

Collapse -

by george123 In reply to Access -Restricting the q ...

Thank you JRod86 for your prompt advice!
It did work fine indeed and also made go deeper with the study of Joins and how to refer to a position in a Combo box.
Good luck in your profession!

Related Discussions

Related Forums