[AccessD] Old-Time Member

Mon Oct 19 16:31:04 CDT 2020

I was wondering about the use of the cross tab.  You could also make some
sub queries - one that selected the data for each month and then make
another query that used the individual monthly sub queries as it's input
and walla, you have all 6 months on a single row.  And it's probably
editable since it's not aggregate.  I used to use queries within other
queries all the time.

Good luck!

GK

On Mon, Oct 19, 2020 at 4:17 PM Keith Williamson <
KWilliamson at designcollective.com> wrote:

> 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
>

-- 
Gary Kjos
garykjos at gmail.com