Use a number from last quarter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a query where I have to find a value from the previous quarter. I
have a way to do this, but it all falls apart in the first quarter of the
year when the previous quarter occured in a different year. Is there a
simple way to tell Access to find the previous quarter taking into account
the fact that years change?
Melinda
 
Hi Melinda,

I don't know if this is what you want but it may help point you in the right
direction:

SELECT Format(OrderDate,"yyyy - q") AS Period, Count(Orders.CustomerID) AS
[Count of Customers]
FROM Orders
GROUP BY Format(OrderDate,"yyyy - q")

You can run this against Northwind and it should work. It groups the orders
by year and quarter and counts the number of customers. You might be able
to modify to do what you need.

HTH

Jamie
 
Hi Jamie,
This certainly will help. I've never seen this function before and my
biggest problem has been getting things sorted by quarter within the year, so
this should be great!
Thanks!
Melinda

Jamie Richards said:
Hi Melinda,

I don't know if this is what you want but it may help point you in the right
direction:

SELECT Format(OrderDate,"yyyy - q") AS Period, Count(Orders.CustomerID) AS
[Count of Customers]
FROM Orders
GROUP BY Format(OrderDate,"yyyy - q")

You can run this against Northwind and it should work. It groups the orders
by year and quarter and counts the number of customers. You might be able
to modify to do what you need.

HTH

Jamie


Melinda said:
Hi,
I have a query where I have to find a value from the previous quarter. I
have a way to do this, but it all falls apart in the first quarter of the
year when the previous quarter occured in a different year. Is there a
simple way to tell Access to find the previous quarter taking into account
the fact that years change?
Melinda
Click to expand...
Click to expand...
 
OK, gave this a try, but it didn't solve the 1st quarter to 4th issue.
Access doesn't understand the new year correctly, so I can't go back to a
previous year with this function either. :(
Melinda

Jamie Richards said:
Hi Melinda,

I don't know if this is what you want but it may help point you in the right
direction:

SELECT Format(OrderDate,"yyyy - q") AS Period, Count(Orders.CustomerID) AS
[Count of Customers]
FROM Orders
GROUP BY Format(OrderDate,"yyyy - q")

You can run this against Northwind and it should work. It groups the orders
by year and quarter and counts the number of customers. You might be able
to modify to do what you need.

HTH

Jamie


Melinda said:
Hi,
I have a query where I have to find a value from the previous quarter. I
have a way to do this, but it all falls apart in the first quarter of the
year when the previous quarter occured in a different year. Is there a
simple way to tell Access to find the previous quarter taking into account
the fact that years change?
Melinda
Click to expand...
Click to expand...
 
Hi,
I have a query where I have to find a value from the previous quarter. I
have a way to do this, but it all falls apart in the first quarter of the
year when the previous quarter occured in a different year. Is there a
simple way to tell Access to find the previous quarter taking into account
the fact that years change?
Melinda
Click to expand...

Depending on how you store your data, there are several. "A" number?
How do you identify which number? How do you store your years and
quarters - in a Date/Time field, in integer fields, or some other way?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Hi Melinda,

The query I posted will return data across years. Is it that you are
wanting quarter one to start at a particular time of the year (i.e. January
instead of July or something like that)?

Have a think about what John Vinson posted and then maybe visit the
following to get some ideas on how to solve your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;210604


Jamie

Melinda said:
OK, gave this a try, but it didn't solve the 1st quarter to 4th issue.
Access doesn't understand the new year correctly, so I can't go back to a
previous year with this function either. :(
Melinda

Jamie Richards said:
Hi Melinda,

I don't know if this is what you want but it may help point you in the
right
direction:

SELECT Format(OrderDate,"yyyy - q") AS Period, Count(Orders.CustomerID)
AS
[Count of Customers]
FROM Orders
GROUP BY Format(OrderDate,"yyyy - q")

You can run this against Northwind and it should work. It groups the
orders
by year and quarter and counts the number of customers. You might be
able
to modify to do what you need.

HTH

Jamie


Melinda said:
Hi,
I have a query where I have to find a value from the previous quarter.
I
have a way to do this, but it all falls apart in the first quarter of
the
year when the previous quarter occured in a different year. Is there a
simple way to tell Access to find the previous quarter taking into
account
the fact that years change?
Melinda
Click to expand...
Click to expand...
Click to expand...
 
Depending on how you store your data, there are several. "A" number?
How do you identify which number? How do you store your years and
quarters - in a Date/Time field, in integer fields, or some other way?
Click to expand...

I am storing them as Dates in a Date/Time field.
 
I am storing them as Dates in a Date/Time field.
Click to expand...

Ok; could you give me a little help with my other questions?

In a table you might have 18423 records entered during the last
quarter. I know, you probably don't - but you *could*.

What "number" do you want to look up? Which of the 18423 records
contains it, and how can you tell?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
OK, let me clarify. I have a database that tracks the maintenance on my
company's vehicles. At the end of each quarter a report is generated that
summarizes each vehicle. One of the columns is Quarter's Miles. So, what I
have to do is take the vehicle's mileage from this quarter and subtract the
mileage from last quarter. So each number is identified by it's vehicle.
Now, I can easily do this for the second and third quarters. However, the
first and fourth are causing me issues. You see, I have my query pull out
only the dates from this year so I don't accidentally get mileage or repairs
from the third quarter of last year (for example). The issue comes when I
have to span a year. Currently my database works as I want, but I have 3
different queries to accomodate the changes of the year. I'd like to only
have to have one query. I hope that clarifies things. Any ideas?
Melinda
 
OK, let me clarify. I have a database that tracks the maintenance on my
company's vehicles. At the end of each quarter a report is generated that
summarizes each vehicle. One of the columns is Quarter's Miles.
Click to expand...

Is this a calculated column, or is it stored in the table somewhere?
So, what I
have to do is take the vehicle's mileage from this quarter and subtract the
mileage from last quarter. So each number is identified by it's vehicle.
Now, I can easily do this for the second and third quarters. However, the
first and fourth are causing me issues. You see, I have my query pull out
only the dates from this year so I don't accidentally get mileage or repairs
from the third quarter of last year (for example). The issue comes when I
have to span a year. Currently my database works as I want, but I have 3
different queries to accomodate the changes of the year. I'd like to only
have to have one query. I hope that clarifies things. Any ideas?
Click to expand...

I suspect that a DSum() or DLookUp() function call can do it. Since I
do not know how your table stores the value that you want to extract,
I don't know the exact expression; but you should be able to do this
all in a single query, using the desired quarter as a parameter! Could
you open one of your queries in SQL view and post it here, so I have a
clearer handle on how your tables and queries are structured?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John Vinson said:
Is this a calculated column, or is it stored in the table somewhere?
Click to expand...

I calculate the quarter's miles on the report, actually. My query just
spits out the current and previous quarters' mileages.
Could
you open one of your queries in SQL view and post it here, so I have a
clearer handle on how your tables and queries are structured?
Click to expand...

Here's my 2nd/3rd quarter query. It's the one that works like I want all
the quarters to.

SELECT tblVehInfo.Vehicle, tblVehInfo.Location, tblVehInfo.Color,
tblVehInfo.Style, tblVehInfo.Year, tblVehInfo.[Purchase Date],
tblVehInfo.[Beginning Mileage], tblVehInfo.[Gross Purchase Amount],
tblQuarterly.Date, DatePart("q",[Date]) AS Expr1, tblQuarterly.[Current
Mileage], tblQuarterly.[Current Value], crsMileageLastQ.[Qtr 1],
crsMileageLastQ.[Qtr 2], crsMileageLastQ.[Qtr 3], crsMileageLastQ.[Qtr 4],
crsQuarterlyRep.[Qtr 1], crsQuarterlyRep.[Qtr 2], crsQuarterlyRep.[Qtr 3],
crsQuarterlyRep.[Qtr 4], crsYeartoDate.[Total Of Cost of Repair]
FROM (((tblQuarterly INNER JOIN tblVehInfo ON tblQuarterly.Vehicle =
tblVehInfo.Vehicle) INNER JOIN crsMileageLastQ ON tblVehInfo.Vehicle =
crsMileageLastQ.Vehicle) INNER JOIN crsYeartoDate ON tblVehInfo.Vehicle =
crsYeartoDate.Vehicle) INNER JOIN crsQuarterlyRep ON tblVehInfo.Vehicle =
crsQuarterlyRep.Vehicle
WHERE (((DatePart("q",[Date]))=DatePart("q",Date()-90)));

Hope this helps.
Melinda
 
Back
Top