complex query question

[mr moe]

hi all,
I have a question hoping that someone can provide assistance. I have a huge table that consist of almost 10,000 records. The three columns that I need to look at are as follows:
computer_serial, change_type, call_date.

change_type can be: add, move, remove

i need to show all records with change_types = remove. Only if remove is the latest status.
for example if I have a computer that has two records one remove and one add, but add has call_date bigger than remove, then i don't want to show it in a query. thanks.

 

[KeithG]

You need 2 queries. first make a query and group by the PK and use the max function on the call_date

Next make a query and join it to your first query by PK. Now add your criteria to this query.

 

[mr moe]

thanks, this is a start, but the problem is that the PK is different for each record, the only thing that is common is the computer_serial_num, i'm trying to group by computer_serial with call_date = max but not luck still!!!

 

[KeithG]

thanks, this is a start, but the problem is that the PK is different for each record

That is definetly not a problem. If there were duplicate PK's that would be a problem. Can you post an example db and I will make your query?

 

[mr moe]

here is a sample copy, look at the query that i need to work on. I need to group by maxium call_date and change_type:= remove. thanks.

 

[KeithG]

I do not see a call date field? Also I see some normalization issues and there are no relationships in your db.

 

[mr moe]

oh, sorry the field is effective_date from change_order_requester. regarding normalizing, this database is mostely imports from excel therefore, this is the best i was able to do. thanks. give me any suggestions!

 

[mr moe]

did anyone fiqure out how to do this complicated query? thanks.

 

[KeithG]

Is there a one to many relation between change_order_requestor_tbl and change_order_tbl? It looks as if there is. The effective date is in change_order_requestor_tbl which is the 1 side of the relationship so there is only going to be one effective date. Is this correct?

 

[mr moe]

yes one effective date per change_order, but you can have another change order for the same serial_num with different effective_date. If you look at the query I have, you will see that i have for example two serial_num with two effective_dates, i want to show the maximum effective date only if it's change_type is "remove"

 

[mr moe]

yes one effective date per change_order, but you can have another change order for the same serial_num with different effective_date. If you look at the query I have, you will see that i have for example two serial_num with two effective_dates, i want to show the maximum effective date only if it's change_type is "remove" else show all serial_num with "remove". Man i'm going crazy!

 

[KeithG]

Look at query result, is this what you wanted?

 

[mr moe]

thanks for the effort, but this didnt' work, for example serial_num:
CNBM086433 should not be showing because there is another record for serial number CNBM086433 with a date that is bigger than the one showing in your query, that has a change_type "add", here is what i want to do.
I want to show all records with change_type = "remove" but if there is a newer record with different change_type then it should be not be shown, look at this below:

Serial Number Change Type change_order_effective_date
CNBM086433 Remove 3/23/2007
CNBM086433 ADD 4/1/2007

for example CNBM086433 should not be in the query because the latest effective date is 4/1/07 and the change type is not "remove"

thanks.

 

[mr moe]

Experts Please Help

Experts Please Help.