Query counting similar items from several fields (1 Viewer)

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,310
I use exactly the same type of approach as in MajP's last example.
Normalised table used to store data but temp 'crosstab' style table used for user entry and display.

I was interested to see this as I hadn't previously seen anyone else using the same approach

I also use this method for both room and equipment bookings and for other examples including parents evening appointments and student attendance registers
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,291
Normalised table used to store data but temp 'crosstab' style table used for user entry and display.
This is by far my biggest gripe with Access. If they provided an unbound data grid, a lot of these kludgy workarounds would be very easy to do. In vb.net there is a datagridview and it is amazing all the things you can do with it.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,310
Whilst it is indeed a workround, the outcome is I find very successful.
Its also extremely fast in use

The Flexgrid control can be used for this kind of application but AFAIK its never been updated to work with 64-bit Access. Peter Hibbs has loads of Flexgrid examples both at UA and elsewhere all with very detailed documentation. Several examples here http://www.rogersaccesslibrary.com/forum/RSS_hibbs-peter_forum21.xml
 

MajP

Access MVP
Joined
May 21, 2018
Messages
2,291
I have used the Flexgrid, but it is painful. Hard to register, not very user friendly, not portable, and limited. A native control could solve those issues. These other techniques work well, but are not the easiest or intuitive for most people to implement. A native unbound data grid would be a common intuitive solution to overcome many of Access's GUI limitations.

Another technique I forgot to mention is building an on the fly in-memory ADO recordset. This way you can have an editable recordset and bind it to a form, then you can add and edit, then use it to populate a normalized table.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,310
I agree with all of that.
The workaround does work well but isn't easy for anyone new to Access to implement.
About 5 years ago I was just beginning to get the hang of Flexgrid when the 64-bit issue led me to scrap everything I'd done based on it

By chance, I'm currently working on using a disconnected ADO recordset which is what I think you're referring to. I'm using that approach with a split no strings database. Again, not intuitive, at least for me, but its an interesting approach.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom