Dealing with period variances

[AccessKay]

I need some conceptual help please. My end result is that I want to set-up
criteria in a form that will do comparisons for month to month, this year’s
month to last year’s month, and this year’s Qtr to last year’s Qtr. For
example, I’d like to choose Month1 and choose Month2 and then the results of
the variances would display in a report (along with Month1 and Month2 data).

Do I need to create separate cross-tab queries for month, year and qtr and
then input expressions for the variance? I can’t wrap my head around it. I’m
not sure what needs to be in the query and what I should leave to do in the
report.

Always thankful,
Kay

 

[KARL DEWEY]

Do I need to create separate cross-tab queries for month, year and qtr
If your data is the same as your last post then you will need to use a union
query to correct it before proceeding.
Post your data structure showing table and field names with datatype. Also
sample data and example of how you would like the results to look like.

 

[AccessKay]

Hi Karl,

From my last post, I had two tables of data with similar fields and I didn’t
want to combine them because I had already created a bunch of time consuming
reports. So I learned how to do the Union query from you so that I could
create a report for the totals from each table and I thought I was all set.
My next step was to build a dialog box so I could rid myself of pop up
parameters. But then that got me to thinking that if I had all my data
combined in one table then maybe I wouldn’t have to build so many queries and
reports for month, Qtr, YTD. So I created a new database with the two tables
combined and restructured my parent tables. The table that I combined is
from an Excel import that I’ll update on a monthly basis. I had the help of
Daryl, another fine soul that answers questions in this forum, and completed
a dialog box with vba code that gives me a report based on my criteria. So
I’m moving on to the next step and that is to create reports that show the
variances from month to month, current month vs. last year’s month and
current year’s quarter vs. last year’s quarter. I’d like to have a dialog
box to generate these reports as well.

Though, I am still struggling with my decision to combine the tables because
though I don’t have to create so many queries and reports, I’m not that
pleased with my reports showing all columns, whether it has data or not.

So here are the specifics. My table is as follows:

tblTrans_Mstr
ID (autonumber)
TransDate (date/time)
Category (text)
Group (text)
Product(text)
PA_Description(text)
Credit_GL_Acct (text)
Lbr_Hours(number)
Labor_Cost(number)
Lbr_OH_Rate (number)
Lbr_OH_Cost(number)
ODC_Cost_Category(text)
ODC_Cost(number)

The only cost I don’t have in this table is Total Labor cost which is Labor
cost plus OH cost. I’ll need this cost for all of the reports.

I’d like to go into my dialog box, and select critieria for Category & Group
and also the periods of time. So for month to month, I’d like to input month
one and input month two and have a report to show the variance between month
one and month two. Then I’d like to enter year and month 1 and enter year
and month two and have the variance in a report. And lastly, enter current
Qtr and then enter Qtr from the previous year and have the variance in that
report. I don’t know if this is the best way of going about completing this
but it’s all that I can come up with. I’m free and flexible to any
suggestions you may have.

Thanks for helping me on this!

Kay

 

[KARL DEWEY]

I do not know the relationship between Category (text), Group (text), and
Product(text) but it does not seem that you should need to enter them when
entering labor and ODCs. Also PA_Description(text) should be at some higher
level.

Is it organized this way -
Category
Group
Product
or like this -
Product
Group
Category

I would setup a table(s) to define the above so that a single field is used
in labor/ODC resords relating back to the definition.

Why Credit_GL_Acct (text) in the same record as labor and ODCs?

My earlier suggestion was to create your table in such a way as to use a
field to define the record as labor or ODC and let the rest of the field
serve dual purposes. Let the record relate back to the project/product and
have the description/definition there. Join the defining tables in your
query if that information is need at the time, otherwise there is no need to
record the description for every labor/ODC entry.

What about employee identification? Overtime? Speciality or hazard pay?

 

[AccessKay]

Hi Karl,

You guys are so smart that at times I forget you’re not mind readers, though
you sometimes come close I do have separate tables defined for all the
fields in text. And yes, I probably don’t need to include the “descriptiveâ€
data fields in the table that I posted. And I do have a field for ODC and
Labor. It’s the field called Group. My other important field is Category
which is either Certification or Trng Dev. Labor and ODC will always be
grouped by these two categories. So my priority is:

Category
Group

And that’s about it (the other fields will be descriptive). This is my
first database that people other than myself will use. I’ve been tasked to
build more complicated databases but thought I’d start with the simpler one.
Because others will be using it, I’m trying to make it as user friendly as
possible, which is something I have not focused on before. Basically, the
purpose of this database is for reports. The data in table I gave you comes
from Excel. So now I want to build reports as follows:

Labor by month to month comparison for current year
ODC by month to month……
Total………
Labor by Qtr vs Qtr for current year (and ODC and Total)
Labor by current year Qtr vs previous year Qtr (and ODC and Total)

I thought maybe I need to build six crosstab queries. Three for Labor
Month, OCD Month and Total Month and then three for Labor Qtr, ODC Qtr, and
Total Qtr. But don’t know if this is the most efficient way???

My hopes are to tie these reports into a user friendly dialog form. I’m
stuck on how to design my queries so that it will be easy to select criteria
in my dialog box. And then I’m wondering about where I put the variance
calculation. I would assume the report but not sure about that.

I guess this is quite a big task for one thread so please just help me with
the query part.

I really appreciate your thoughts and suggestions.

Kay