[AccessD] Old-Time Member

Mon Oct 19 16:36:59 CDT 2020

Hi Keith,

I was asked once to set up a method to allow engineers to enter their hours by project and by day.  So for each week they would have multiple projects per day and each day had to have 8 hours of time.

I did use a crosstab query to create the data structure for display, but like you're seeing I couldn't use that as the datasource for an editable form.  To make it work, I set the crosstab query to populate a temporary table and set the temporary table as the data source for the form.  When the form was edited by an engineer, that triggered some code that modified data in the permanent table, triggered the crosstab query, repopulated the temp table, and redisplayed the form.  Took some work but it ran well!

Good luck with your project!
Dan

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson
Sent: 19 October, 2020 16:16
To: Access Developers discussion and problem solving
Cc: Gustav Brock
Subject: Re: [AccessD] Old-Time Member

Thanks all....a couple good responses.  I think either a temporary table, or using Excel as the front end....Looks like I'm going to have to exercise my frontal lobes on remembering how to do all this.  😊

The reality is the Crosstab is for presentation only....there is only one number in the source data per column/row intersect.  I'm setting the data up such that there is a column from project number, column for month, and column for amount.  So...if I have 6 months of projections for a project, there are six rows of monthly projections.  I'm just wanting to present that as one row of projections, with 6 columns (for each month) across the top.  Ideally....the project manager could go in and edit those projects (as presented.)  Albeit...the PM will probably have multiple projects...so they could have multiple rows (one for each project) x 6 columns.  I'll play with all your suggestions to see what might work best.  May be more questions coming.

Thanks for the time to respond!

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

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Gustav Brock via AccessD
Sent: Monday, October 19, 2020 2:10 AM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Cc: Gustav Brock <gustav at cactus.dk>
Subject: Re: [AccessD] Old-Time Member

[THIS EMAIL IS FROM AN EXTERNAL SENDER]

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://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_GustavBrock_VBA.Round&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=RvpYxUTFCg21jpL8CBhMlP8Vf_h2bXdGfglwICJUf-I&s=5iu2MrB4mvnPs-5oTi6fUJIyLAb3oSQnfYblxarLsaI&e=

/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://urldefense.proofpoint.com/v2/url?u=https-3A__twitter.com_designcollectv&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=RvpYxUTFCg21jpL8CBhMlP8Vf_h2bXdGfglwICJUf-I&s=lzBxw9c8lo0N85b3AsB2lOV6ZwDWlenJPbOw6fWhQIc&e=>[cid:image003.png at 01D6A39B.859B8AB0]<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.linkedin.com_company_design-2Dcollective-2Dinc._&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=RvpYxUTFCg21jpL8CBhMlP8Vf_h2bXdGfglwICJUf-I&s=NhZgUEmLFEpVPIRgN4WRgU5V8a3AAt5tp4fNKQCKdHM&e=>[cid:image004.png at 01D6A39B.859B8AB0]<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.instagram.com_designcollectv&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=RvpYxUTFCg21jpL8CBhMlP8Vf_h2bXdGfglwICJUf-I&s=sXxRyBLa3H0zYQWg9gYVINmGknBpNzhZdClmX5bmMNs&e=>

--
AccessD mailing list
AccessD at databaseadvisors.com
https://urldefense.proofpoint.com/v2/url?u=http-3A__databaseadvisors.com_mailman_listinfo_accessd&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=RvpYxUTFCg21jpL8CBhMlP8Vf_h2bXdGfglwICJUf-I&s=pWI5aVnWrq1UIBAQkHcH2656B-gvEVmEHnu0v2VWlDo&e=
Website: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com&d=DwIFAw&c=euGZstcaTDllvimEN8b7jXrwqOf-v5A_CdpgnVfiiMM&r=cCa049KXg2Due8I6xkXzoVrfvG83jjeYLf7LB9M_3qY&m=RvpYxUTFCg21jpL8CBhMlP8Vf_h2bXdGfglwICJUf-I&s=_6nfuvbbUtQahzQxfOQgskSOqImy-nGUsj7eC-SKiCg&e=

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com