Trying to Combine Multiple Queries into 1 main Query

[gold007eye]

Can anyone help me with how I can accomplish this?

Here is what I currently have:

10 SQL Pass-through queries to update different Date field (Date1, Date2, Date3, etc.) based on the value of the next date field.

This is how each individual query is set up (there are 10 in total)

UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));

Here is what I would like to have: (as 1 query)

UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));

UPDATE tblApplications SET tblApplications.LastContact = [date2]
WHERE (((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Null) AND ((tblApplications.[Date Completed]) Is Null));

UPDATE tblApplications SET tblApplications.LastContact = [date3]
WHERE (((tblApplications.[Date Completed]) Is Null) AND ((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Not Null) AND ((tblApplications.Date4) Is Null));

Can I somehow combine them together like this or do I have to keep them as 10 seperate queries?

 

[pbaldy]

Hopefully this isn't a duplicate, as I got an error the first time I tried to reply. The short answer is no, you can't combine multiple update queries into one (in Access anyway). The long answer is that the repeating date fields are not a proper normalized design. You should probably have a separate table for contacts, where each contact is represented by another record in this new table rather than a field in the existing table. From there, finding the latest is simple.

 

[pbaldy]

By the way, I'm not saying you couldn't come up with a way to do this in one query, either with a huge IIf() or a function or something, but it would be a hack solution to overcome improper design. The correct route is to fix the design.

 

[gold007eye]

I will see if I can get it normalized. (I'm not very good at that part) How would I link up the contacts table with the main table to make sure the data showed correctly? I am taking over a currently designed database; and I'm trying to make improvements upon it.

 

[pbaldy]

Normalization is vital to a smooth running application, so it's worth spending the time to get it right. I assume that the applications table has some sort of key field (ApplicationID or something along those lines), so the contacts table would have a field for that, a field for the date/time of the contact, and perhaps a notes field if you wanted to jot down any notes. The 2 tables would be related by that key field, one to many.

The "normal" way to display this type of data would be a form/subform, where the form contained data from the application table and the subform contained the contact data. The master/child links of the subform would keep the 2 in sync with each other (ie whatever application was displayed on the main form would have its contacts displayed in the subform).

 

[gold007eye]

Thanks for the help I will try and see if I can get this normalization figured out and do some searches on here.