Help with query

[boboleft]

I have a MS access table that contain data which I manually compared to find out the status change between the current week and the last status before it got changed. I am looking to automate this process as much as possible. How can I change the query to output the status changes as listed below??

Big thanks in advance for helping me out...



ReportDate Child# status
1/1/2010 6801 active
2/1/2010 6801 active
3/1/2010 6801 active
4/1/2010 6801 inactive
2/1/2010 6802 Pending
4/1/2010 6802 Active

Output

Child# Old status New Status
6801 active Inactive
6802 pending active

 

[MazeWorX]

Take a look at a crosstab query. The access query wizard can walk you through it

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[boboleft]

Thanks. I have tried to do it by building a query but it doesn't look like it work. Is there any other ways to do this??

 

[JoeAtWork]

I don't see how crosstab would accomplish the OP's aims since he is not turning a field value into a column heading.

I think I would approach this with a UNION on two queries. The first query would have the most current record for each Child#, the second query would retreive the second most current record for each Child#. The first query would be easy, just group by Child# and use Max(ReportDate). The second query would be much harder (and I can't think of SQL at the moment). But assuming you have figured out those two queries, then you could UNION them something like:

SELECT [Child#], "" AS [Old Status], Status AS [Current Status] FROM Query1
UNION
SELECT [Child#], Status AS [Old Status], "" AS [Current Status] FROM Query2

 

[dhookom]

The first step would be to create a query that numbers the records by most recent for each Child and Status. This would then allow you to filter to only the 1s for active and pending for use in a crosstab.

It would help to understand how often status changes and what significance there is to "current week". Is there always only one value per week?

Duane
Hook'D on Access
MS Access MVP

 

[IvanhoBlanc]

I think this query can be (a part) of the solution
Success

SELECT TestTable.Child, TestTable.ReportDate, TestTable.status
FROM TestTable
WHERE (((TestTable.ReportDate) In (SELECT TOP 2 ReportDate
    FROM TestTable as S
    WHERE S.Child = TestTable.Child
    ORDER BY ReportDate DESC
)))
ORDER BY TestTable.Child, TestTable.ReportDate;

 

[dhookom]

Create a self join query [qrnkbobo] to rank the records within each ChildNum (I can't force myselft to type # into a field name ;-)

SELECT boboleft.ReportDate, boboleft.ChildNum, boboleft.Status, Count(boboleft.ChildNum) AS CountOfChildNum
FROM boboleft AS boboleft_1 INNER JOIN boboleft ON boboleft_1.ChildNum = boboleft.ChildNum
WHERE (((boboleft_1.ReportDate)>=[boboleft].[ReportDate]))
GROUP BY boboleft.ReportDate, boboleft.ChildNum, boboleft.Status
ORDER BY boboleft.ChildNum;

Then create your crosstab query:

TRANSFORM First(qrnkbobo.Status) AS FirstOfStatus
SELECT qrnkbobo.ChildNum
FROM qrnkbobo
GROUP BY qrnkbobo.ChildNum
PIVOT Choose([CountOfChildNum],"New Status","Old Status") In ("Old Status","New Status");

Duane
Hook'D on Access
MS Access MVP

 

[boboleft]

thanks for everyone's help.

 

[boboleft]

Hi,

I have tried the above queries but it didn't really work. I kept getting error message..not sure what I did wrong.

 

[dhookom]

didn't really work" and "getting error message" has no value to us. If you want some help, tell us what you tried and the exact error message or results.

Duane
Hook'D on Access
MS Access MVP

 

[boboleft]

Hi dhookom,

I am so sorry, I should have included the error message with the post. After I have created the first query, a box prompt for me to enter the report date (enter parameter value: boboleft.ReportDate) I think maybe I have done something incorrectly..but haven't been able to figure out. Thanks so much for your help.

 

[PHV]

enter parameter value: boboleft.ReportDate
You really have a table named boboleft ?????

 

[boboleft]

I just tried to keep consistent with the example. The table name is actual named something else.

 

[dhookom]

Why can't you post the actual SQL as well as your actual table and field names?

Duane
Hook'D on Access
MS Access MVP