Dumb girl, can't do simple query with 1 calculated field (1 Viewer)

[BustyAshley]

Hi all,

I need a simple query or queries that will do the following.

First summarize the data by company name (Easy Part) then calculate the monthly value from 2 year to date values (Date field is text). I am going to append this list to another table to give me a monthly calculation by company.

The Current Period = Forms![Dashboard]![cboMonth]

My idea to pull the prior period was using another table [A2 Month Index]
that looks below, and use a DLookUp??

Current ,, Prior
006.2015 ,, 005.2015
007.2015 ,, 006.2015
008.2015 ,, 007.2015

See Example below - thx ash


Data Table:

Contract ,, Company ,, Period ,, YTD Value
1 ,, A ,, 007.2015 ,, 7
1 ,, A ,, 008.2015 ,, 8
2 ,, B ,, 007.2015 ,, 7
2 ,, B ,, 008.2015 ,, 8
3 ,, B ,, 008.2015 ,, 1
4 ,, C ,, 007.2015 ,, 14
5 ,, C ,, 007.2015 ,, 7
4 ,, C ,, 008.2015 ,, 16
5 ,, C ,, 008.2015 ,, 8
5 ,, D ,, 008.2015 ,, 2


Output table:

Company ,, Period ,, MTD Value
A ,, 008.2015 ,, 1
B ,, 008.2015 ,, 2
C ,, 008.2015 ,, 3
D ,, 008.2015 ,, 2

 

[plog]

Date field is text

You've got a few issues and this is a big one. You should store your date values as DateTime fields. Access has special functions that allow you to easily determine what the prior, next, 14th month is from a date, but you have to pass it dates to do that calculation.

My advise is to fix your date data and put it into DateTime fields. If you are only capturing the month and year , then you should make the date for that the 1st of every month (e.g. 008.2015 -> 8/1/2015).

I am going to append this list to another table

That's not how databases work etiher. You shouldn't be storing calculated values, instead you should calculate the values you need, when you need them. The data you want to generate should be only in the query and not in a table.

 

[BustyAshley]

I have 6 files per region x 9 regions = 54 files.

5 out of the 6 files has monthly data with the same period structure. I have a simple Query that pulls the common fields and combines it into 1 Table. (I send this data table out in excel with a simple pivot, combines huge excel files files into like 5-6MB)

This last file however comes to me with a year to date value. I was calculating by using a pivot table in excel, then uploading it to the database. It also has 50 columns of data and is like 90MB (Doing this 9 times crashes my excel a lot)

The process was working until they asked me to bring in another column from this last file. I had to recalculate all the monthly data for 4.5 years (9 files x 12 months x 4.5 years ugh!! SMH!) it took me a whole week.

I was hoping to create a bolt on that would suck in this huge file, then calculate the monthly value, so if this request ever happens again, I could just delete the output table, and re-run the query over and over again. (Or Prehaps just do a lookup off the original data)

 

[plog]

That last post addressed none of my points and sounded like a completely new issue you need to address.

How can I help you?

 

[BustyAshley]

I guess, this isn't a case in storing "calculated values."

It's more of a case of I need to calculated values so I can store the data :banghead: lol.


I have to calculate the monthly values using two YTD values every month to store into the database. I was hoping just to store the huge file in access, run a query or a macro to calculate the monthly values, then store it to the table I have been appending to monthly.

So is the date field the only thing limiting it? Can I use a lookup to convert it to a date, then convert it back after?

 

[plog]

You should be able to use CDate(http://www.techonthenet.com/access/functions/datatype/cdate.php) along with some string functions to convert your existing "Date" to an actual date in a query. Then in another query you can use the other Date functions to find prior, next, etc.

 

[BustyAshley]

Ok ok I think I found a way to solve this issue.

If I run a Crosstab Query, Putting Company as my Rows, and Period as my header. I can run a simple query of that one to calculate the difference between two periods.

Is there a way to set the criteria for the period in the Crosstab query based on a combo box on a form? Then set the Simple Query Field's to dynamically select the two period headers (Or Make the cross tab query rename the two period headers to "Current" & "Prior") Then set my simple query off that query to pull the static names?

If there is a will, there's a way