[AccessD] Old-Time Member

[Gustav Brock]

Hi Keith

Welcome back!

The reason a crosstab can't be edited is, that Access wouldn't know where to put the edits, as the original values are aggregated.
You could write the output of the crosstab to a temporary table, then present this. Those records and fields can be edited, but it will be up to you to decide which table(s) should be updated. 
If the field to be updated is a sum, you can update the addends of this. It's a delicate process to do right, but should you need it, I've prepared it for you. It is the function RoundSum found in my project VBA.Round:

Rounding a series of numbers to a sum
https://github.com/GustavBrock/VBA.Round

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces at databaseadvisors.com> På vegne af Keith Williamson
Sendt: 16. oktober 2020 15:06
Til: accessd at databaseadvisors.com
Emne: [AccessD] Old-Time Member

Hey guys,

Hope you all might remember me.  I've moved on to a new company, this year, and find myself (at least for now) involved again in utilizing MS Access for systems solutions.  My current solution, I am working on, is to develop a planning module (to replace a very archaic Excel model) which collects and stores planned billings, by project, over the next 6-12 months.  I'm looking to provide the PM's with a form that displays (filtered for them individually) their projects and each month's planned billings in a Crosstab format.  So there would be rows of projects (with various information related to the project....Fee, YTD Billings, Backlog, etc).....but then columns of fields for monthly planned billings (Oct, Nov, Dec, Jan, etc.)

The problem I have is that in order to get the format I want, it would need to be a Crosstab query, which isn't editable.   Additionally, I want the form to ONLY show looking forward data.  So, while the data would include what was planned for September.....I no longer want to present the past months on the form....only the current - future months.  But they have to be able to edit, as circumstances change on the projects.

Hopefully this makes sense.  I'm asking if any of you can steer me in the right direction for approaching this?  Should I use MS Access only for the tables needed to present the data, and store the actual planned billings....but use Excel as the front-end?  Can I accomplish this (with variable monthly column headings) on a form, with the fields being editable?

Thanks in advance for any feedback.

Regards,

Keith E. Williamson
Chief Financial Officer

601 East Pratt Street, Suite 300
Baltimore, Maryland 21202
Tel 410.685.6655  | 100% Employee-Owned Design Firm www.designcollective.com [cid:image001.png at 01D6A39B.859B8AB0]
[cid:image002.png at 01D6A39B.859B8AB0]<https://twitter.com/designcollectv>[cid:image003.png at 01D6A39B.859B8AB0]<https://www.linkedin.com/company/design-collective-inc./>[cid:image004.png at 01D6A39B.859B8AB0]<https://www.instagram.com/designcollectv>