Multiple Queries

[james.burden1]

Ok this is going to be hard to explain but i will try my best.

I have a table that has 65,000 records in. It contains data such as
job number, customer name, But it also contains fields called date1,
status1, date2, status2....all the way to 20.

In these fields are the history of dates when the job changed status
from being prepared, in hand, awaiting despatch to complete.

Now these status's can be in any of the status fields 1 - 20. So i
have to write a query that checks the status to see if it is equal to
"C" for example and place the date that it was changed into a table. I
have done this query not a problem but i have another issue.

When i run the first query for the "Datein" it runs and places the job
number, customer name and datein into the table. When i run the "check
for complete" query which does the same but looks for a "C" it appends
the records to the table with a complete date, but now i have two
records with the same job and customer details.

What do i need to put in my queries that makes the data that it finds
add to the existing records in the table?

Example:
SON Customer Name DateIn DateCompleted
12345 John Smith 01/01/2000
12345 John Smith 02/01/2000
12346 John Doe 03/02/2001
12346 John Doe 06/02/2001

I have to do this in 20 queries per status i want to look for.
Otherwise I will end up with 65,000 records duplicate many many times.

Any help would be greatly appreciated on this as it is killing me.

 

[Clifford Bass]

Hi James,

Your database is in desparate need of what is called normalization
where you have a table for customers, probably one for jobs and another for
job statuses.

tblCustomer
SON
Last Name
First Name
etc.

tblJobs
JobID
JobDescription
SON
other information about the job

tblJobStatuses
JobID
DateIn
DateCompleted
other information about the job status

One place to start is <http://allenbrowne.com/casu-22.html>. Click on
the Normalizing Data link.

Clifford Bass

 

[Burbonizer]

Unfortunatly that is not an option. This database was written about 10
years ago and to change it now would be a mammoth task as we have
other database front ends writing info into it.

The more i look at the way it was written the more i think it could
not have been done any other way. I understand that it is best to
split up the info in your tables but as i said this was written by
another person.

Can any one help me on this matter?

 

[Clifford Bass]

Hi James,

That being the case, try something like this, which assumes that any
status can be in any status field (using "I" for the in status; you will need
to expand out to the full 20 statuses where I put the ...s):

insert into tblDestination
([SON], [Customer Name], [DateIn], [DateCompleted])
select [SON], [Customer Name], Switch(Nz([status1], "") = "I", [date1],
Nz([status2], "") = "I", [date2], Nz([status3], "") = "I", [date3], ....,
True, Null), Switch(Nz([status1], "") = "C", [date1], Nz([status2], "") =
"C", [date2], Nz([status3], "") = "C", [date3], ...., True, Null)
from tblSource;

If the in status is always the first one, you can eliminate the first
Switch() function and just select date1. Also, you would not need to check
status1 for the C in the second Switch(). This should be the only query you
need.

Hope that helps,

Clifford Bass

 

[Burbonizer]

Hi James,

     That being the case, try something like this, which assumes that any
status can be in any status field (using "I" for the in status; you will need
to expand out to the full 20 statuses where I put the ...s):

insert into tblDestination
([SON], [Customer Name], [DateIn], [DateCompleted])
select [SON], [Customer Name], Switch(Nz([status1], "") = "I", [date1],
Nz([status2], "") = "I", [date2], Nz([status3], "") = "I", [date3], .....,
True, Null), Switch(Nz([status1], "") = "C", [date1], Nz([status2], "")=
"C", [date2], Nz([status3], "") = "C", [date3], ...., True, Null)
from tblSource;

     If the in status is always the first one, you can eliminate the first
Switch() function and just select date1.  Also, you would not need to check
status1 for the C in the second Switch().  This should be the only query you
need.

                   Hope that helps,

                          Clifford Bass

Unfortunatly that is not an option. This database was written about 10
years ago and to change it now would be a mammoth task as we have
other database front ends writing info into it.

The more i look at the way it was written the more i think it could
not have been done any other way. I understand that it is best to
split up the info in your tables but as i said this was written by
another person.

Can any one help me on this matter?- Hide quoted text -

- Show quoted text -

Ok. I cant quite make sense of any of that so i dont really know what
i need to put in my query nor where to put that syntax.

Could you explain to me where i insert this?

James

 

[Clifford Bass]

Hi James,

Start up a new query and cancel the add table dialog. Then click on
the SQL view button (left end of ribbon/tool bar). Copy my SQL statement
into the window, replacing anything already there. Then fix my SQL to
reflect your actual table names and field names. Expand out the innards of
the Switch() functions where as appropriate to cover the whole twenty status
fields and dates. Change to datasheet view. If all goes well it will work.
If that still leaves you confused, post the names of your source and
destination tables, along with the field names that are involved.

Clifford Bass

Hi James,

That being the case, try something like this, which assumes that any
status can be in any status field (using "I" for the in status; you will need
to expand out to the full 20 statuses where I put the ...s):

insert into tblDestination
([SON], [Customer Name], [DateIn], [DateCompleted])
select [SON], [Customer Name], Switch(Nz([status1], "") = "I", [date1],
Nz([status2], "") = "I", [date2], Nz([status3], "") = "I", [date3], .....,
True, Null), Switch(Nz([status1], "") = "C", [date1], Nz([status2], "") =
"C", [date2], Nz([status3], "") = "C", [date3], ...., True, Null)
from tblSource;

If the in status is always the first one, you can eliminate the first
Switch() function and just select date1. Also, you would not need to check
status1 for the C in the second Switch(). This should be the only query you
need.

Hope that helps,

Clifford Bass

Ok. I cant quite make sense of any of that so i dont really know what
i need to put in my query nor where to put that syntax.

Could you explain to me where i insert this?

James