Architect's Drawing Register with CrossTab

[Mart905]

Hello esteemed Access gurus.

I am in the process of creating a document management system in Access with VBA. Mostly this is going well, but I have 2 problems, (both relating to crosstabs,) and hope someone here will be able to help.

I have provided a simple stripped-down version to share here: It is an architectural drawing register consisting of 2 tables and a crosstab query.

Drawings are listed in tblDrw.

Project-wide Changes are listed in tblChg.

Each time a Change is made to any Drawing(s), a new entry is added in tblChg. It can then be applied to any relevant drawings that were updated as part of this Change

The Crosstab Query/Report shows the way I want to display the information.

Problem #1:

I would like to display a drawing version number as the ‘value’ of the crosstab. This should increment from ‘1’ for each drawing, but only when that drawing is changed.

Currently, the ChangeID field is used as a place holder, but this obviously locks the value to the change, which is not correct. The highlighted values below should be “3” on the middle row, then “1”, “2” on the bottom row.

I have previously tried creating a new field called ‘DrwVersion’ for this, but did not have any luck getting it to calculate correctly.

Any insights appreciated!





Problem #2:

When a new project-wide Change is added, the report will not add the new date column.

From what I’ve read, it sounds like I need to make a dynamic report, but the examples I’ve seen so far have not helped me to achieve this.

In theory, there could be any number of changes added over the life of the project, so I don’t really want to set a maximum.

Any help much appreciated. Thanks!

Martin

[CJ_London]

On my phone so cannot view the db. Suggest post the sql to your crosstab - and have you tried max(version) as the value?

[mike60smart]

Hi Martin

Would suggest that you have a Main Form for your Drawings with a Subform for the Many changes

See the attached

[Micron]

From what I’ve read, it sounds like I need to make a dynamic report, but the examples I’ve seen so far have not helped me to achieve this.

That is only for when a ct query returns fewer fields than what the report contains.

I didn't download the revised version but I imagine it doesn't comply with your desire to layout the data that way. Do yourself a favour and abandon that idea if you want to stay within Access. Otherwise you'll have to output your records to Excel, which can handle the presentation of an ever growing number of columns. For Access, the subform suggestion is the way to go.

[June7]

If CROSSTAB output will vary in the number and names of date headers, then you have to modify the report with every run. Doing this programmatically would be a big challenge.

Could force CROSSTAB to always have same number and names of date headers, even if there is no data.
Have you seen this tutorial https://www.fmsinc.com/MicrosoftAcce...ort/index.html

I agree with mike60, multi-value field is bad and source of your difficulty in numbering changes for each drawing.
With mike60's revised db, CT could be:

TRANSFORM First(DCount("*","tblDrawChanges","DrwID=" & [tblDrw].[DrwID] & " AND ChangeDate<#" & [ChangeDate] & "#")+1) AS GrpSeq
SELECT tblDrw.DrwID, tblDrw.DrwNo, tblDrw.DrwName, tblDrw.DrwScale
FROM tblDrw INNER JOIN tblDrawChanges ON tblDrw.DrwID = tblDrawChanges.DrwID
GROUP BY tblDrw.DrwID, tblDrw.DrwNo, tblDrw.DrwName, tblDrw.DrwScale
PIVOT tblDrawChanges.ChangeDate;

DrwID	DrwNo	DrwName	DrwScale	10/1/2023	10/2/2023	10/3/2023	10/4/2023
1	XX-01	Site Plan	1:200	1
2	XX-02	Floor Plan	1:100	1	2		3
3	XX-03	Elevation	1:100		1	2

If date criteria gives you difficulty, review http://allenbrowne.com/ser-36.html
or change DCount() to use DrawChangesID:
DCount("*","tblDrawChanges","DrwID=" & [tblDrw].[DrwID] & " AND DrawChangesID<" & [DrawChangesID])

I found spelling error in field name: DrawChanngesID. I fixed it to DrawChangesID. I also had to change data in tblDrawChanges to reflect original data and produce example output of the OP.

However, this does not solve issue of dynamic date headers and I again refer you to the tutorial link.

Another thing to explore is CT query as SourceObject for a subreport container control. Unfortunately, since there are no textboxes, cannot format dates with vertical orientation. Quickly run out of horizontal space. Could reduce the date value to just a day number and use filter criteria to limit output to a specific month/year. Could also have month and year as additional row headers and that would allow for multi-year output. Use IN() function to force 31 day headers. Column widths set in query datasheet view will be reflected on the report. Show month/year in report header textbox. Won't be fancy but it works.

[Mart905]

Thank you all for your responses and time given - very helpful and much appreciated.

I have abandoned the multi-value field. Your advice echoes what I have read elsewhere on the internet – they are bad!

I do need the Drawing-to-Change relationship to be effectively many-to-many, so I have added a linking table, which seems to achieve what I need. The Crosstab then looks like this:

TRANSFORM First(DCount("*","tblDrwChg","DrwID=" & [tblDrw].[DrwID] & " AND DrwChgID<" & [DrwChgID])+1) AS GrpSeqSELECT tblDrw.DrwNo, tblDrw.DrwName, tblDrw.DrwScale
FROM tblChg INNER JOIN (tblDrw INNER JOIN tblDrwChg ON tblDrw.DrwID = tblDrwChg.DrwID) ON tblChg.ChgID = tblDrwChg.ChgID
GROUP BY tblDrw.DrwNo, tblDrw.DrwName, tblDrw.DrwScale
PIVOT tblChg.ChgDate;

Regarding updating number of columns on a crosstab. I think I will set up the report with something like 20 columns. If a project goes beyond 20 changes, then perhaps a prompt for user to select a range to display is the way to go?

I will apply what I have learnt to my actual DB and report back with my progress...!

[Mart905]

Hello.

Update on this, just incase it's useful to anyone that comes across this in search for their own solutions to similar problems.

I ended up creating a crosstab report by manually adding 30 date columns and re-naming the header labels and data textboxes appropriately. ("lblDate1" through "lblDate30" and "txtV1" through "txtV30" respectively.)

Label captions all start empty, and textbox ControlSources all start unbound.

My VBA then looks like this:

Option Compare Database

Dim rstChg As DAO.Recordset
Dim d As Date
Dim x As Long
Dim n As Long

Private Sub Report_Load()
      
    Set rstChg = CurrentDb.OpenRecordset("tblChg")
    n = rstChg.RecordCount 'Total number of changes
    rstChg.MoveFirst
    x = 1
    
    If n > 30 Then 'Too many for page
        MsgBox ("Displaying the 30 most recent changes")
        rstChg.Move (n - 30) 'Set the starting record
    End If

    'Populate Dates and Versions
    Do Until rstChg.EOF
        d = rstChg!ChgDate
        Controls("lblDate" & x).Caption = d
        Controls("txtV" & x).ControlSource = d
        x = x + 1
        rstChg.MoveNext
    Loop
    
    rstChg.Close

End Sub

All seems to be working nicely.

Thanks again to you all for the help!

[Welshgasman]

Might want to start using Option Explicit as well?

[Micron]

I don't get the usefulness of rstChg.RecordCount. Unless you first rs.MoveLast you cannot know the record count. At most all you can get from that is 0 or 1. Item 4 here
http://allenbrowne.com/ser-29.html

If you went to the trouble of creating 30 textboxes and 30 companion labels it might help to know that if labels are attached to textboxes, then the label is a member of the textbox controls collection (which only ever contains one item - the label). That can make it easier on you next time.

[Mart905]

Thanks Micron,

That's interesting. Just stepped through it, and contrary to that article, rstChg.RecordCount always returns a value that is +1 more than the number of records (I guess maybe it also counts the blank (new) line?)

Is it possible that my recordset is small enough that all records are loaded before it runs count line? If so, could this end up being problematic when there are more records? Maybe I should add in a .MoveLast line to make sure?

[Micron]

If you can post a zipped copy of your db these things are something I'd like to see. I've yet to encounter a situation where, if using .OpenRecordset, the record count is greater than 1 without moving last. AFAIK the 'new record' line isn't a record until at least one field contains data, and that does not include fields with default values either.

[Minty]

Unless they have altered default behaviour, the only thing you could rely on rs.RecordCount returning (without a move last) was 0 if there were no records, and you may as well use rs.EOF to determine that?

[CJ_London]

seem like a complex way of doing with no guarantee you have the latest - and will fail if there are less than 30 records

I would have used a SELECT TOP 30 query, ordered by changed date desc which is then crosstabbed, then just looped through the report recordset field names to populate the header captions - xtab query much as you have in post #6.