Show only records that have changes

[trident]

Please bear with me on this, so you can see it all



I have 1 table named BLData, this table have several fieldnames - Status, ActivityNo, ActivityName, Status Date, Original duration, Remaining Duration, Total Float.

The table is running around 10000 records.

1. The Status have several codes - BL, Update1, Update2, Update3...........
2. Activity No - duplicate numbers are allowed (not the primary key)
3. Original Duration, remaining Duration and Total Float - this are numeric data

I created individual query to get the ActivityNo, ActivityName, OrigDuration,RemainingDuration,Total Float based on the Status and combine them into 1 query so I can arrange in report and form as shown below:

Actvity No	Activity Name	Baseline	Update1	Update2	Update3	Update4	Update5	Update6
DZ1BDDAR01	Architectural design	50	45	40	35	45	40	50
DZ1FIFAR005	Architectural approvals	20	20	20	18	15	11	18

here is my requirement,

1 - considering that there are hundreds of updates, how can I arrange the form/report as shown above without creating a query?
2 - how can I print the activity as shown above if the update goes up compared to the previous update (needs to show in the form/report only those that have changes that went up compared to the previous update)

Hope you can help me on this.

Trident

[June7]

Can define grouping and sorting with report design.


Getting data from other records of same table requires nested subquery or domain aggregate functions.

Review: http://allenbrowne.com/subquery-01.html#AnotherRecord


If you need to compare data in same record - probably will need a custom VBA function. Changes that went up considering which updates - only the last two?


This data structure is not normalized.

[trident]

Thank you again, for patiently pointing me to the right direction.

Actvity No	Activity Name	Baseline	Update1	Update2	Update3	Update4	Update5	Update6
DZ1BDDAR01	Architectural design	50	45	40	35	45	40	50
DZ1FIFAR005	Architectural approvals	20	20	20	18	15	11	18

Will working on the reply sent, I am also working on conditional formatting, the conditional formatting should be as:
BL<Update 1 then red colour
Update 1 < Update 2 then red colour
Update 2 < Update 3 then red colour

I was able to get the correct result for
BL<update 1

however, in the succeeding, it did not work.

help is really needed.

Thanks again

[June7]

What does 'did not work' mean - error message, wrong results, nothing happens?

Need something to review. Post code or database.

[trident]

P6Updates.zipThere is no code.

'Did not work means' the colour did not changed when it should be.

i am attaching the database:

below are the data needed:

TABLE - BL and ActivityName
QUERY - 1BL to 1U18 combined to
tOD1-6, tOD7-12 and tOD13-18 combined to
RODAll
FORM - RDuration Option Colour

the conditional formating is in the Form.

Thank you for checking the database.

[June7]

I don't understand the form structure.

Textbox named StatusU1.OriginalDuration is bound to field StatusU1.RemainingDuration - why is it not bound to field StatusU1.OriginalDuration?

The formatting rule references OriginalDuration but the form has 18 fields named OriginalDuration from various queries. Need to specify which one.

[trident]

If you notice the Form (Rduration Option Colour), I have 18 updates for 18 queries.

I created the queries, because I cannot get the correct data for lets say 2nd Update, I cannot get the correct data to appear. that is why i created 18 queries for each update.

Update 1 needs to be compared to BL - it should change the colour if UPDATE 1 > BL
Update 2 needs to be compared to update 1 - it should change colur if StatusU1.RemainingDuration>StatusU2.RemainingDurat ion
Update 3 needs to be compared to update 2 - it should change color if StatusU2.RemainingDuration>StatusU3.RemainingDurat ion

This changing of colours are made by conditional formatting.

[June7]

That doesn't change my comments.

Still confused. Why does that form have textboxes bound to the same fields in both form and page header sections?