Impossible Query?

R

robboll

Is there any way to do this?


You have data that looks like this:

CODE DATE OPER CASE_TYPE COLOR
A 31-Aug-97 G99 T RED
A 31-Aug-99 G99 T RED
A 31-Aug-01 A22 T RED
A 28-Feb-03 P11 T RED
A 31-Aug-03 G99 U RED
A 31-Dec-03 G99 U BLUE
A 31-Aug-05 G99 U BLUE
A 30-Nov-05 G99 U BLUE
A 31-Aug-07 S56 U GREEN
A 30-Sep-07 S56 U GREEN
A 01-Jan-99 S56 U GREEN


You want the row values to disappear if no change. Like this:

CODE DATE OPER CASE_TYPE COLOR
A 31-Aug-97 G99 T RED
31-Aug-99
31-Aug-01 A22
28-Feb-03 P11
31-Aug-03 G99 U
31-Dec-03 BLUE
31-Aug-05
30-Nov-05
31-Aug-07 S56 GREEN
30-Sep-07
01-Jan-99

I am trying to find the best method of doing this. Thanks for any
suggestions.

RBollinger
 
J

John W. Vinson

Is there any way to do this?


You have data that looks like this:

CODE DATE OPER CASE_TYPE COLOR
A 31-Aug-97 G99 T RED
A 31-Aug-99 G99 T RED
A 31-Aug-01 A22 T RED
A 28-Feb-03 P11 T RED
A 31-Aug-03 G99 U RED
A 31-Dec-03 G99 U BLUE
A 31-Aug-05 G99 U BLUE
A 30-Nov-05 G99 U BLUE
A 31-Aug-07 S56 U GREEN
A 30-Sep-07 S56 U GREEN
A 01-Jan-99 S56 U GREEN


You want the row values to disappear if no change. Like this:

CODE DATE OPER CASE_TYPE COLOR
A 31-Aug-97 G99 T RED
31-Aug-99
31-Aug-01 A22
28-Feb-03 P11
31-Aug-03 G99 U
31-Dec-03 BLUE
31-Aug-05
30-Nov-05
31-Aug-07 S56 GREEN
30-Sep-07
01-Jan-99

I am trying to find the best method of doing this. Thanks for any
suggestions.

Base a Report on the query; set the Duplicate Values properties of the
textboxes appropriately. For onscreen use Preview the report, and of course
for paper just print it.

It's very, very difficult to do this in a Query but then you shouldn't be
looking at query datasheets anyway, other than for debugging your application.
 
J

John Spencer

Impossible? NO.
Very, Very difficult? Yes
Slow with any reasonable size data set? Yes
Very slow with a large data set? Yes, possibly to the point of timing out.

A bad idea? Yes

If you can impose a unique order on the records, you could probably
write a query with multiple subqueries (or use multiple VBA domain
functions) to return Null or the value depending on the previous record
based on the unique order.

Why do you need to do this? If it is for a report, see John Vinson's
suggestion as it is very easy to implement. Here is a short sample
based on just the date field being used to identify a unique record.
This is liable to fail with a TOO COMPLEX error. And if you need to
identify the previous record based on DATE AND CODE things get more complex.

SELECT IIF(
(SELECT First(Code)
FROM [YourTable] as A
WHERE A.[Date] =
(Select Max([Date])
From [YourTable] as B
WHERE B.[Date] < C.[Date]))=Code,"",Code)
, [Date]
, << Another subquery for field OPER and each of the
Other Fields>>
FROM [YourTable] as C


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top