473,324 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Insert Month

I have a crosstab report with running totals monthwise for a product. However if a product does not have a movement for any month, the cumulative total as at that monthend is not displayed in the report. The report does not bring forward the previous monthend balance. How to solve this issue though the report displays the totals for all the months correctly where there is a movement for that month.
Jun 8 '11 #1
1 1720
Stewart Ross
2,545 Expert Mod 2GB
It depends on what the value you are pivoting upon is. If it is very simple, such as a month number ranging from 1 to 12, you can define static column headers for the pivot field (by going to the pivot field's properties in design view and setting the Column Headings property to a list of all possible headings, like this:

1, 2, 3, 4, 5, 6, 7, 8 , 9, 10, 11, 12

If you use a more complex column heading for the pivot you will need to generate all possible values for that column heading in a separate query, and then left-join those values to the crosstab so that there will always be a row for the particular month you need.

Unfortunately, Access cannot lookup pivot headings dynamically in response to changing circumstances - a weakness with crosstab queries.

There is a slower method to get what you want without using a crosstab at all, but to advise further on this method (which uses a series of manually-defined fields for each month to SUM the relevant value only if a particular month condition is true) I'd need to see the current SQL for the crosstab you are using at present.

-Stewart
Jun 12 '11 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: MAB71 | last post by:
I'm running an ISP database in SQL 6.5 which has a table 'calls'. When the new month starts I create a new table with the same fields and move the data of previous month into that table and delete...
9
by: adi | last post by:
Hi all, Hope there is a quick fix for this: I am inserting data from one table to another on the same DB. The insert is pretty simple as in: insert into datatable(field1, field2, field3)...
0
by: Alexander Vlasenko | last post by:
Please CC me, I am not subscribed. An imaginary SQL statement INSERT INTO table FETCH ... FROM cursor; looks almost the same as currently available INSERT INTO table SELECT ...; I tried it...
6
by: Hasanain F. Esmail | last post by:
Hi all, I sincerly thank you all in advance for your help to solve this problem. I have been trying to find a solution to this problem for sometime now but have failed. I am working on a...
6
by: Laura Stout | last post by:
Please help! I've been spinning my wheels on this one! I am working on a database that tracks future project opportunities. For each opportunity, the data entered includes total project value and...
7
by: Novice Computer User | last post by:
Hi. Can somebody PLEASE help. I have spent hours on this.. but I am a total novice and can't seem to figure it out. Here is a .php script. Right now, the minimum amount of time (i.e. duration)...
3
by: assgar | last post by:
Hi I am having problem with my loping. I don't know if I have chosen the correct approach. GOAL: I need to insert into a table event types for a specific date range. The calendar the event...
4
exedotsree
by: exedotsree | last post by:
Hi Pls help me find out the syntax error in the following stmt... Dim insqry As String = "INSERT INTO AssShift(employeeid,departmentid,day" & curdate.Day & ",month,year,lastupdated) VALUES (" &...
1
by: lyter | last post by:
hi..i'm newbie in sql..i have a big problem..i'm used db access and asp code in my system..hope someone can help me to fix this issue.. here i have two table..1) eachclick 2)month 1st (in table...
0
by: marlberg | last post by:
Platform: Windows2000, WindowsXP, Windows Vista, etc Language: C#, ASP.NET Pre-compiled Libraries: Enterprise Library 3.0 full I have a requirement to implement in and display in C# and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.