Crosstab Type Form?? (1 Viewer)

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Hello,

I have a table for an annual budget that requires me to enter 360+ amounts for various budget codes.

A crosstab query displays the information perfectly and will be great to use for display purposes, however does anyone know how I can make a similar layout for data entry on a form?

Cheers
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Should have said the crosstab query looks something like:

xxxxxxx 4000 Training 5000 Expenses
Jan 2021
Feb 2021
Mar 2021

Etc..
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:04
Joined
May 21, 2018
Messages
8,463
Can you post a screen capture of your crosstab? That may help to provide some ideas.
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
1632644604011.png
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Ideally want something like this that I can edit....you can see I've a lot of data to enter and I just want to see it clearly
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Only thought I've had is to do a normal form with this crosstab as a subform within it that then requeries on update on the main form......it not ideal though
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 19, 2013
Messages
16,553
your problem is that a crosstab does not contain a unique ID as such (same as an aggregate query).

One solution would be to create a temporary table or a disconnected ADO recordset based on your crosstab and edit that.

In terms of updating the underlying budget table, this could either be done for each change as and when they occur in the control afterupdate event, or with a button to save all changes or the form close event.. You would need need to identify the appropriate record to update by referencing the value in your BudgetMonth column and the column name. The code would also have to decide whether it is going to update an existing record, or append a new one (and maybe delete if the new value is null)

You might want to consider swapping columns and rows - with months across the top - get too many nominal codes and you will run out of screen estate, plus the crosstab and form will need modifying every time you add, delete or change a nominal code.

Edit: you should also ensure that your budget table has a multifield index set to unique so you cannot end up with two or more records for the same month/nominal code
 
Last edited:

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Hi thanks for this, I'd actually changed the rows/columns because of that. Noted about the lack of ID in the crosstab....explains why you can't update it.

It will only be me updating the budget so maybe I'll live with the awkward way of doing it, it's only once a year after all! Sounds a like this will take more time than worth it to find a work around!
 

isladogs

MVP / VIP
Local time
Today, 12:04
Joined
Jan 14, 2017
Messages
18,186
Here is an example of something similar from one of my commercial apps for schools:

1632654519607.png


This is based on a crosstab query from normalised data
It uses dynamic header captions so these can be updated automatically. The headers are vertical so more columns can be fitted in the available space.
Doing the above shouldn't take too long.

However, do bear in mind that crosstab queries are read only.
If you want the form to be editable (as in my example), you need to save the crosstab query results to a temp table and use that for your form's record source. Then after making any data changes, those will need to be saved back to the original normalised data table. That is a lot of extra work
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Thanks for this, agreed a lot of extra work for something that will take me a few hours a year to complete.

Thanks again for all your help
 

isladogs

MVP / VIP
Local time
Today, 12:04
Joined
Jan 14, 2017
Messages
18,186
Whether its worth it is only something you can judge. You might find the challenge worthwhile in itself :)
It would probably take you several hours to create in the first place.
However, once you've made it, you will be able to reuse it each time without any additional work. So eventually it will save you time.

Alternatively, export the data to Excel & do the processing there
Good luck
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Thanks for that, yeah I'd thought of doing it like that in exporting it.

Another issue......Haha! Today is full of them!!

So I have 2 queries that look like this:

qry1

Jan 22 $50000
Feb 22 $100000
etc... until Dec 22

This is my budget totals for each month.

qry2

Jan 22 $45000
Feb 22 $90000

This is my actual totals for each month, but obviously the months only go as far as the date I've put expenses in (IE if we're only in March then there will be no values for the rest of the year)

I need to somehow join these two queries together so that I can make a graph from them that compares Budget Vs Actual, it has to then put a zero figure in the months where I've not come to in the year for expenses.

Does that make sense? Does anyone have any good suggestions! Last part of my DB then I'll stop hassling you guys for a bit!! haha
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
I could do this with an Iif statement but unfortunately the date in qry2 is from a totals table so it groups them by month.....any ideas
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
I have the following SQL:

SELECT DISTINCT [2022 Monthly Totals].[Budget Month], [2022 Monthly Totals].SumOfBudget, IIf(DatePart("m",[Date By Month])=DatePart("m",[Budget Month]),[Sum Of Home Currency (USD)],0) AS Actual
FROM [2022 Monthly Totals], [tblExpenses Query1];

But it still shows duplicate rows....

1632661424425.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:04
Joined
Sep 21, 2011
Messages
14,038
Where are the duplicates?
Actual is different in each case? :unsure:
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
I want it to only show one Date (each month of the year) and next to it show the budget total (which is already entered for the whole year), then in the next column I want the actual spent.....this is where it goes wrong, I tried to put an Iif statement in so that if no expenses were made that month then it inserts a zero, I really want it to look like:

Month Budget Actual
Jan 22 10000 5000
Feb 22 100000 40000
Mar 22 10000 0
April 22 5000 0

Etc....

This example would be where there has been no expenses put in after Feb
 

mounty76

Registered User.
Local time
Today, 05:04
Joined
Sep 14, 2017
Messages
341
Instead the IIf statement always puts a zero in for every month regardless is there is any actual spend or not, thats why the above shows two results for the months where there is an expense
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:04
Joined
Sep 21, 2011
Messages
14,038
Why can't you just Group by Month? and Sum relevant fields?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:04
Joined
Feb 19, 2002
Messages
42,970
Here is an example that uses properly normalized tables and a bound form. Only a couple of lines of code required.. The PDF is an explanation of how it works.
 

Attachments

  • BoundDenormalizedForm_20210319.zip
    1.5 MB · Views: 221
  • Bound Denormalized Forms.pdf
    351 KB · Views: 188
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:04
Joined
May 7, 2009
Messages
19,169
qry1

Jan 22 $50000
Feb 22 $100000
etc... until Dec 22

This is my budget totals for each month.

qry2

Jan 22 $45000
Feb 22 $90000

This is my actual totals for each month, but obviously the months only go as far as the date I've put expenses in (IE if we're only in March then there will be no values for the rest of the year)
Create a New query Left Joining Qry1 To Qr2 On [Month Budget].
 

Users who are viewing this thread

Top Bottom