J
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.
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.