View Most Current Month Data

[brorick]

I have a table that consist of the following fields:

DepartmentName
Jan
Feb
Mar
April
May
June
Total

The user enters their stats for the month in the corresponding month. Does anyone know how to run a query and display on the most current months data. Therefore if the user has only entered data for Jan, Feb, and Mar then the Total column will display only the data for Mar. I was able to achieve this in Excel using offset, but I am not having much luck in Access. :doh: Any thoughts? Thanks in advance.

[OBP]

Hello again, to display just the last months data create a query with the month as one of the fields, set the view totals and in the Month field set it to Max. You may need to use that query to "filter" the data in a subsequent query if you can't do all the calculations in the "max" query

[matthewspatrick]

Any thoughts?

My thought: this is a very bad table design. Your design should go more like:

DeptID
Month (represented as a date, and not text)
MonthValue

With a design more similar to that (i.e., closer to normalized), answering this kind of question becomes trivial. For example, to get last month:

SELECT *
FROM YourTable
WHERE [Month] = DateSerial(Year(Now()), Month(Now()) - 1, 1)

[brorick]

OBP and Matthewspatrick thank you for your help. I would agree that my layout was not the best approach. Unfortunately I created the table to satisfy the user. The user wanted the layout of their report to appear as follows.

Depart. Name Jan Feb Mar Apr May Jun Jul Total(Current Month)
HR 10 8 7 5 5
Cust. Svc. 8 8 4 9 9
ISD 6 11 9 8 8

I decided to follow your recommendation and redo the table structure. Of course now the formula DateSerial(Year(Now()), Month(Now()) -1, 1) works like a charm.

I do however still have a layout dilema. My data on my report now appears as follows. I am grouping by dept. name.

HR
Jan 10
Feb 8
Mar 7
Apr 5
Total 5 (Current Month)

Cust. Svc
Jan 8
Feb 8
Mar 4
Apr 9
Total 9 (Current Month)

ISD
Jan 6
Feb 11
Mar 9
Apr 8
Total 8 (Current Month)

Does anyone know how to use my new table structure and still be able to display the data as in the first example above. My table structure is:

DeptID
Month (represented as a date, and not text)
MonthValue

:mkay I just can't get my new layout to work in the requested report layout. Your help is appreciated. Thank you.

[OBP]

Use a Crosstab query with the Dept as row headings and the Months as Column Headings.

[brorick]

Of course. I never had a need to use a crosstab query before therefore it never came to mind. I appreciate the recommendation. I will definately give it a try. Thanks.

[brorick]

OBP, the crosstab query was my solution. Yeah it worked. :bow:

I do however have a quick question. Do you know how I could get the formula, = DateSerial(Year(Now()), Month(Now()) - 1, 1) to appear in the total column. I want the value for the previous month to appear in the total column. Would I achieve this in the crosstab query or the report level? I want the values for all of the months to appear on the report, but the total at the end of the row should only show the most current months value.

[brorick]

I got the value I needed to appear in my crosstab query, but unfortunately it is listing the referenced row twice. I have attached a copy of an Excel spreadsheet that shows the crosstab query layout and the results. Hopefully someone can point out what I am doing wrong. Thanks.

[OBP]

I think that you may have a problem with the query column shown in the Excel sheet in column f, because it is shown as a Row Heading.
I would really need to have the table(s) and query(s) to test itto be sure.

[brorick]

OBP thank you for your help. I have attached a sample copy of the database.

[OBP]

Shouldn't the total for the last month be the sum of all the rows?
Or do you just want to repeat the last month with a value in it on the right hand side of the report for each row?
The seperate row is being caused by the Group by in the Last month Total calculated column.
It may be possible to do what you want in the report now that you have the crosstab with totals.

[brorick]

Some of the categories in the table should have a total that reflects a sum of all months while others should show only show the most current months total. I was really hoping to achieve this in the report, but I have had no luck.

[OBP]

If you could give me an exact example of what you want to achieve I will see if I can do it for you.

[brorick]

OBP, thanks again for your help. I have attached an Excel spreadsheet that shows a layout of a sample report.

[OBP]

Well that is a wierd setup.
That is achievable using the standard CrossTab that you created first.
The placing of the last Month's data can be done in the report's "Detail" on format event procedure. The most basic way would be to just have a text box where you want that value to appear.
In the event procedures VB just have a simple list of lines of code something like
if [Jan]>0 then textbox = [Jan]
if [Feb]>0 then textbox = [Feb]
down to
if [Dec]>0 then textbox = [Dec]
whichever month is last to be entered is the last to leave it's value in the text box.

[brorick]

OBP, where would I place this code. Originally I attempted what you are recommending. But, I abandon it because I could not figure out where to put the code. I will create a text box on the form titled "Total" and place it in the Details section. Where would the code go?

[OBP]

If you click on the "bar" that seperates the header from the detail and then right click it you will find the event procedures listed, use the "On Format" one.

[brorick]

Thank you. I will give this a try.

[brorick]

I am having difficulty thinking through the logic on the select statement. I have to figure out how to have my select statement verify that the flag checkbox = yes and if the december field = 0 then the total box equals the previous months value unless that months value is also 0 look at the previous month and so fourth. The select statement should continue to circulate through until a month is located with a value and then that value will display in the total box.

flag = yes
dec = 0
nov = 0
oct = 0
aug = 25
total = 25

Here is my select statement for a reference for the one filed but I am not sure how to include the other fields.

Select Case [curmoflag]
Case yes And Me.dec = 0
[total] = Me.nov
Case Else: [total] = Me.SumOfMonths
End Select

Any thoughts.

[OBP]

I am not sure what the "flag" is but asuming that it is not part of the selection for each month this is how I would do it.
If me.Jan> 0 then [total] = me.jan
if me.feb>0 then [total] = me.feb
if me.mar>0 then [total] = me.mar
and so on.
This means that the last month with a value greater than zero leaves it's value in the total field.
you may be able to [Jan], [Feb] as well or whatever the report field names are.