Project list How This Works About Us Contact

MS Access Newbie Question

Reward: $0.50

Ideas posted: 2

79 views

I'd like to create a query expression that displays the variance between [Percentage] field entries in the same table based on a date and an office. There is a percentage entry for multiple offices (distinguished by [OfficeCode]) and multiple entries per office distinguished by [ReportDate].

So the table looks something like this:

[OfficeCode] [Percentage] [ReportDate]

Off1 90 1/1/11

Off2 95 1/1/11

Off1 93 2/1/11

Off2 92 2/1/11

The result I want to see is:

Off1 3

Off2 -3

I've created two queries to separate the date entries into separate tables, then an additional query based on those to calculate the variance, but I am getting duplicate results for some reason. I don't want to have queries creating separate tables either if there is some way to avoid that.

I've been Googling around a bit but haven't found anything that addresses this specifically enough for me to apply it and I suppose I'm being impatient.

Please give me some ideas / instructions on how to best set up a query to give me the results I'm looking for.

Category: Computers & Internet

Tags: access date expression ms access queries query variance

Idea submissions are immediately visible to the public.

Post idea

Thanks BusinessGuy, but I want it in Access because 1) I am trying to learn the program and 2) connecting this with other database data by office code is a better fit in Access. (sidenote)Yes, Access and Excel can link data.

Feb 10, 2011

I'm forwarding this to my teacher, lucky you I'm taking MS Office as a class this semester. Hopefully will have an answer for you within the week. I can only answer for Access 2010 ver.

Feb 12, 2011

This idea would be a lot easier to solve in Excel -- then you could just use simple subtraction equations and sums if you want to expand your tables.

Doesn't Excel link with Access? I've never had to do it but I would try it out if it's possible.

Feb 10, 2011