Required solution for simplification of data entry in a table or form (1 Viewer)

[ddvicky]

Dear Forum,

I have three tables in access 2007 as follows:

1. Customer Details: Which contains the customer details such as, customer name, address, city, state, phone, email, website, contact person, contact person number, contact person email, and finally web address.

2. Plant Details: This table is being used to store the information of machines which customer purchase from us. This table contains the fields as Customer Name (The Data in this Field will be looked up from Customer Name field in Customer Details Table), Site Address, City, State, Machine Model, Site Contact Person, Site Contact Number, Site Contact Details and like this. Means this table is being created to store the data of all the machines at different addresses for the same customer.

3. Daily Entry: This table is being used to store the data of daily visits of engineers to the above plants of customers. and have fields like, Customer Name, Date of Visit, Address, Plant Model, Plant Serial Number,

Now i have query for my third table.

1. I wish to select customer name from the customer table for which i can apply lookup properties in relation to customer details table

2. When i have selected a customer name from lookup through customer details table. I have three plants for the same customer for which the data has been stored in plant details table. For one customer i have stored three plants at three different addresses.

I want that if i have selected a customer name, address should display only those three addresses which are associated to that customer. But it is displaying all the addresses stored in plant details table for all the customers.

Is there any procedure of setting a query to solve this problem.

Waiting for your kind suggestions.

Best regards,

Deepak Sharma

 

[pr2-eugin]

Hello Deepak, Welcome to AWF..

What is the Query you currently have in place?

 

[ddvicky]

Dear Sir,

Thanks for your reply.

Query is as follows:

SELECT [Plant Details].Address FROM [Plant Details], [Daily Entry] WHERE ((([Daily Entry].[Customer Name])=[Plant Details]![Customer Name])) ORDER BY [Plant Details].Address, [Daily Entry].[Customer Name]

Please be noted that i am totally new to Access 2007, i have read Microsoft Access 2007 step by step book, and doing through that only. The above query has been built up through query design wizard in row source property in lookup field properties in design view of the table.

For your kind consideration.

Best regards,

Deepak Sharma

 

[pr2-eugin]

You need to JOIN the two tables.. Something along the lines of..

SELECT [Plant Details].Address 
FROM [Plant Details] INNER JOIN [Daily Entry] ON [Plant Details].[Customer Name] = [Daily Entry].[Customer Name]
ORDER BY [Plant Details].Address

 

[ddvicky]

Dear Sir,

I have tried the query as suggested, but still all the addresses are being displayed in combo box.

This will not be feasible for me, as when i will have total 100 plants, then combo box will display 100 addresses, then it will be easy to type the address rather than finding and selecting.

Regards,

Deepak Sharma