Emulate Reports' Grouping Levels with Forms

[NauticalGent]

Afternoon everyone,

As the title suggests, I am trying to do something "cute" with a form by doing something similar with the grouping feature found in Reports. I know it cant be done as it is done with reports, but I'm hoping there is some sort of hack that can be done to emulate it.

I've attached a copy of what is I have done so far but it is not working. Specifically, the list box on the subform is not populate with the applicable CLINs associated with the Contract Number.

Appreciate any ideas...

 

[jdraw]

NG/John,

No relationships in your design?
Any info re your desired format?

 

[NauticalGent]

Hi Jack, this was a quick throw together the "primary" dev handed off to me. Let me fix that bit and I will post that and the desired look.

 

[MajP]

If that is really needed, then this may be an OK case for a subdatasheet.

 

[MajP]

You could concatenate all of that into one textbox, but that would have limitations. How about a treeview?

 

[Josef P.]

If the data does not need to be edited, you can also use a subreport in the form.
Or maybe use an html table in a webcontrol. (Webcontrol unfortunately does not work in a continuous form. That would bring me back to the report. )

 

[NauticalGent]

I don't have SnagIt on this computer so I couldn't dress the graphic up. In the List box I want the applicable CLINS associated with the applicable contract to show. In the case of the first record, CAGE 00724, the subform lists the Contracts for that CAGE (SPRMM113G1101THA8, SPRMM113G1101THA9, SPRMM113G1101THAA etc). Each of those Contracts have any number of CLINs. In the case of Contract Number 'SPRMM113G1101THA8', the List box should be popluated with CLIN number '0001AA' and other info as well.

Because the Subform is continuous, I cant place a Subform or report there without Access converting it to a single form which is what I do not want.

DB with relationships attached

 

[NauticalGent]

Now that I have taken a step back, the table design isnt all that great. Going to normalize it better and see if that helps me get there.

 

[Josef P.]

Should it look like this?

 

[jdraw]

Seems Josef P has a working example.

 

[Josef P.]

I just used a subreport to show the target layout.
Subreport replaces sfm_OTD_AssigmentDetail.

 

[NauticalGent]

Should it look like this?

View attachment 106493

Nailed it! Now tell me how you did it...

 

[Josef P.]

Note: I deleted all the data in the tables because I wasn't sure if it was just sample data.

 

[NauticalGent]

Note: I deleted all the data in the tables because I wasn't sure if it was just sample data.

No worries. I did try the subreport/subform thingy too, but as I said, I got a warning messages telling me that by adding a subform TO a subform, it would have to convert the first subform to single from continuous.

I have to step out for a few but I will look at what you did as soon as I get back.

Appreciate you looking at it

 

[CJ_London]

it would have to convert the first subform to single from continuous.

Yes - but you can set it back again to continuous

 

[CJ_London]

just had a play around with your query - came up with this sql

SELECT t_Contracts.ID, M.[CONTRACT NUMBER], t_Contracts.CLIN, Max(t_ContractData.[CLIN Due Date]) AS [MaxOfCLIN Due Date], First(t_OTD_Status_Codes.Status) AS FirstOfStatus
FROM ((t_Contracts INNER JOIN t_ContractData ON t_Contracts.ID = t_ContractData.ContractID) INNER JOIN t_OTD_Status_Codes ON t_ContractData.StatusCode = t_OTD_Status_Codes.ID) LEFT JOIN (SELECT t_Contracts.[CONTRACT NUMBER], Min(t_Contracts.ID) AS MinOfID
FROM t_Contracts
GROUP BY t_Contracts.[CONTRACT NUMBER])  AS M ON (t_Contracts.[CONTRACT NUMBER] = M.[CONTRACT NUMBER]) AND (t_Contracts.ID = M.MinOfID)
GROUP BY t_Contracts.ID, M.[CONTRACT NUMBER], t_Contracts.CLIN
ORDER BY t_Contracts.ID, M.[CONTRACT NUMBER], t_Contracts.CLIN, Max(t_ContractData.[CLIN Due Date]);

which produces this result

Pretty sure the sql could be simplified, but didn't quite understand what you are trying to achieve. Basically just added a minID query and left joined to it.

edit: just realised I did not include Cage - assuming a contract can only have one cage, include it in the min query. if not, you'll need another min query for cage and id.

 

[gemma-the-husky]

II don't know if this is the same, but when I wanted a financial report to be sorted/grouped in an arbitrary order, I offered sort selections in the form, and stored the derived sort string as a single string in the query.

so if you wanted branch, cost dept, you would store a sort string thus

01-12
01-17
01-23
02-12
02-17
02-23

But it you wanted cost dept, branch you would store

12-01
12-02
17-01
17-02
23-02
23-02

So one query can provide all the alternative sort requirements.

 

[CJ_London]

just added a thread here about forms as a report

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