Hide extra data in continuous form rows

[Kayleigh]

Hi there

Currently working on recreating a spreadsheet for a client in MS Access so the data displayed is dynamic.

Their spreadsheet is like this:

Based on a query I have created this:

Not sure if this is possible but would I be able to hide the days where appears multiple times?

 

[theDBguy]

You can in a report but not in a query.

 

[Minty]

You can if you employ some clever conditional formatting....
I'm trying to find an example, but I seem to have lost it.

 

[Kayleigh]

I'm also thinking there can be a logic to get this working. For example if I number each row and hide any numbered above 1.
I'm sure it would be possible somehow.

 

[Minty]

If only Access had a inbuilt row number function, things like this would be trivial.
Unfortunately, it doesn't, so you have to get cunning with it. I'm still trying to find the database I did this in...

 

[The_Doc_Man]

The problem is the source of data in what you see.

In Excel, you see the actual contents of the cells. Those empty cells are EITHER invisible or they really ARE empty. Each cell is an entity unto itself. If those cells WERE really empty and you went back to sort things, you would see the blank cells in nonsensical places.

In Access, what you see is GENERATED from an underlying query. That grid is a predefined internal form. The minimum to make this work would be some other column that allows you to sort within groups. (Like Minty's "inbuilt row number" comment, which doesn't actually exist.) IF you had a way to count rows with the same key, you could build a hidden field containing that row number and just conditionally format anything that wasn't on a sub-row numbered "1." You could also do it if you had a secondary sort field and could run a correlated query to tell you how many rows precede the row you are in based on that secondary sort field. Without a row number or secondary sort field, you can't get there from where you are.

 

[MsAccessNL]

Do you mean, Monday followed by 3 empty days and then Tuesday followedby 1 empty day?

 

[Kayleigh]

Yes - so each day appears once only.

 

[CJ_London]

You need a more sophisticated query - take a look at this thread

Making a form look like a report

As a result of this post https://www.access-programmers.co.uk/forums/threads/emulate-reports-grouping-levels-with-forms.326697/ I thought I would diddle around and come up with some examples. This is a simple format - only shows the customer once and uses conditional formatting to change the...

www.access-programmers.co.uk

 

[MsAccessNL]

You can use a Temp table as recordsource of the form. Load your query in a recordset and Loop the recordset to put the values in the Temp table, the double days are replaced with a null string. I can give you the code if it’s to difficult. I think it’s also possible with sql, i have to check my vba vault.

 

[GPGeorge]

Yes - so each day appears once only.

I would attempt to solve this problem by creating a Union Query that has two SELECTs.

In the first, I'd add the "filtering field" you need and give it a value of say, 1. This SELECT would be grouped so that only the first record for the filtered value appears, e.g. the first instance of the weekday.

In the second, I'd add the "filtering field" you need and give it a value of say, 0. This SELECT would not be grouped, but would exclude the initial record used in the first select.

Then you could use conditional formatting to "hide" the "0" values.

However, that's not something I've tried, so it might be even more complicated than simply creating a report which supports this display format natively.

 

[CJ_London]

further to post #9

look at the query qryFormReportSimple and the only mods you need is to make is to not show the nwdCustomers CustomerName field (but leave for sorting) and remove the grp1field as not required as that is only required for form formatting.

which produces a query like this

 

[GPGeorge]

Yes - so each day appears once only.

Also, it occurs to me that, since this is supposed to be a spreadsheet, you could apply conditional formatting in that resulting spreadsheet to achieve your desired display.