473,324 Members | 2,124 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.

Percent Difference From

Windows 7 / Access 2007

I have data in a table that consists of these columns.
Product ID, Product Name, Vendor, Location Delivered, Price, Date(mmm-yy)

The data goes back 2 years.

I am looking to find the percent increase/decrease between the same product ID that came from the same vendor and delivered to the same place month to month.

I was able to do this easily in Excel (pivot table) using the "%difference from" in the field settings and selecting Date and using the previous date.

I am looking to do the same thing in Access but am having trouble finding the equivalent. Ultimately I would like to add this calculation to the pivot table in Access.

Thanks in advance.
Jun 30 '11 #1
8 3597
Rabbit
12,516 Expert Mod 8TB
First you'll need a query that aggregates your data into months. Then you can join that to itself to get the data for the prior month.
Jun 30 '11 #2
Could you be a bit more specific about "aggregate your data into months"?

Wouldn't I still need to create some type of match criteria because I only want the prices to be related to each other if all other fields are matching.
Jun 30 '11 #3
Rabbit
12,516 Expert Mod 8TB
I don't know the specifics of your data but most likely a sum query grouping by the month, year, and product id.

I don't know what you mean by your second sentence.
Jun 30 '11 #4
Jerry Maiapu
259 100+
Your table structure is not normalized therefore performing your request would be quite confusing.
If you already have data in your table then you already have duplicates of Product ID which violets normalizations rules.

Vendor and location delivered should be on a different table. If you wish to keep a record of year’s or month’s price, then Date and Price should be on a different table. The main table should have the fields: Product ID and Product Name. So you would have three tables having 2 one to many relationship to the Main Table.
Until then you achieving the “Second sentence” (Referred to by Rabbit)ie your response post
will be quite confusing as the table structure itself is not in order.
Jul 4 '11 #5
So once I normalize the table how will I be able to get the excel equivalent for "percent difference from"
Jul 5 '11 #6
Rabbit
12,516 Expert Mod 8TB
Refer back to post #2 and #4
Jul 5 '11 #7
What is a sum query? I thought there was update, append, delete, and make table query. Is there anyway you can be more detailed in the instruction?

Thank you
Jul 6 '11 #8
Rabbit
12,516 Expert Mod 8TB
A SUM query is of the form:
Expand|Select|Wrap|Line Numbers
  1. SELECT |FieldName1, ...| SUM(FieldName2)
  2. FROM tableName
  3. |WHERE FieldName3 = '...'|
  4. |GROUP BY FieldName1, ...|
  5. |HAVING COUNT(*) > 1|
Where the sections enclosed by pipes are optional.
Jul 6 '11 #9

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

Similar topics

14
by: Edward Diener | last post by:
In the tutorial on functions there are sections on default arguments and keyword arguments, yet I don't see the syntactic difference between them. For default arguments the tutorial shows: def...
4
by: Bill | last post by:
I have some values that I want to display as percent, such as the retail price/wholesale price. In some instances, the wholesale price is zero, so I get a division by zero error. What can I do...
4
by: Stubble | last post by:
When I wrote up a module to display the Windows Print Dialog box (to allow the user to select a specific printer on the network) instead of the DoCmd.OpenReport the function worked fine. I packaged...
1
by: Alan Lane | last post by:
Hello world: I'm having trouble finding how to escape the percent sign ("%") from a SQL query so that it will show up in an Access report. Here's my SQL string ... '--Build SQL String...
23
by: thebjorn | last post by:
For the purpose of finding someone's age I was looking for a way to find how the difference in years between two dates, so I could do something like: age = (date.today() - born).year but that...
3
by: Davide | last post by:
Hi all. I'm looking in php.net a function that helps me to work with date. I need to build a script wich can calulate the difference from a specific date "2007-03-01" to current date. I'm...
5
by: cecil | last post by:
Hi All, I have a report sectioned/grouped by year with number fields (i,e attendance). I would like to calculate percent difference of attendance for each year. Thanks in advance, Cecil
2
by: idealfellow | last post by:
I am using the following to get the time: puts Time.now --> starting of my script <MY CODE> puts Time.now --> end of my script I want to get the Time difference between two Time.now...
4
by: nityaprashant | last post by:
Hello.. i have two dates one is trdate e.g. "2008-02-20 12:55:25.000" (sql server format) another one is currdate system current time (date.now) if time passed more than 30 minutes than trdate...
10
Dököll
by: Dököll | last post by:
Hey Guys! I ran into a puzzle; below query gets me what I need (Difference from two time fields) in Ms Access 2000 but not in MS Access 2003, any idea why: SELECT Data.ID,...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.