Query Help Required

[rivergum_23]

Hi All,
Just wondering if anybody can give me help to do the following. As you can see from the database, the query is set to display "Customer A" job from the Jobs table. (I'll add a form and combox to select which customer I want to query, report button etc but for the purpose of this example I'll leave as is) Then what I want to do is if there are jobs for Customer A in the jobs table to perform a query in the"Contacts" table on Customer A and return the email address in the report. Of course if there isn't any Customer A jobs in the table, nothing is required to happen. Sounds easy enough, but cannot quite work out how to do it. Please help!

Thanks Heaps in advance,


River

[R_Badger]

I have downloaded your file, taken a look and made a few changes.

By the looks of things you need to get up to speed on data normalisation.

You labelled ID as your primary key, nice start, but not very descriptive, I have renamed the ID's to something relevant for each table.

I then replaced you duplicate customer field in jobs, with the customer_ID

I then created a relationship between the two.

I then built a new query (easier than rebuilding the old one)

I then built a new form.

For customer I changed it to first name, last name as an example, however customer would work fine for a company name, but would be less than ideal for an individual (back to data normalisation)

To sum up, I strongly recommend reading up on normalisation and database design before you progress too far, your tables and their structure will determine the ultimate success of failure of your project as well as determining the difficulty of updating it in the future.

I recommend reading this on database normalisation
http://www.barrywise.com/2008/01/dat...gn-techniques/

[rivergum_23]

R_Bader,
Thanks so much for your assistance, you time and effort is very much appreciated. The only problem is that I have to keep the tables in the same arrangement/format which I uploaded because the tables come from another software package and linked into Access via ODBC, so I cannot make any changes to the tables. With that in mind are you still able to get the information across to the report as you did in the updated access upload??

Thanks Again,
River

[rivergum_23]

Hi Everybody,
Is someone able to help me with this one?? Not sure if it's possible, but I will repost my intal msg;

Hi All,
Just wondering if anybody can give me help to do the following. As you can see from the database, the query is set to display "Customer A" job from the Jobs table. (I'll add a form and combox to select which customer I want to query, report button etc but for the purpose of this example I'll leave as is) Then what I want to do is if there are jobs for Customer A in the jobs table to perform a query in the"Contacts" table on Customer A and return the email address in the report. Of course if there isn't any Customer A jobs in the table, nothing is required to happen. Sounds easy enough, but cannot quite work out how to do it. Please help!

Thanks Heaps in advance,
River

One thing to note is that I cannot change the format of the tables because this is how I receive the data from our ERP.

Any help much appreciated.

River

[recyan]

In total agreement with R_Badger. Apart from that, in line with R_Badgers suggestion, just try a join on the Customer Field, perhaps something like below :

SELECT 
	Jobs.ID, 
	Jobs.Customer, 
	Jobs.Part, 
	Jobs.Qty, 
	Contacts.Email
FROM 
	Contacts 
	INNER JOIN 
	Jobs 
	ON 
	Contacts.Customer = Jobs.Customer
WHERE 
	(((Jobs.Customer)="Customer A"))
ORDER BY 
	Jobs.Customer;

If possible, definitely try to implement R_Badger's suggestion with whoever is giving you the data.

Thanks

[rivergum_23]

Recyan,
Ended up getting a bit of help with this one and we come up with the same solution as you.

Thanks a lot for your help

River

[recyan]

Thanks