Impossible Query?

  • Thread starter Thread starter robboll
  • Start date Start date
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
 
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.
Click to expand...

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.
 
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
'====================================================
 
Back
Top