Hi John,
When there are multiple readings on a day, what I want to do is summarize them to produce a net change for the site for the day. Then I want to display in a continuous form one row for each site with the daily changes for the current day and the preceding seven days.
I have the query for the summary working. However, when I use this summary query as a subquery for the crosstab to produce the data for the continuous form, this message pops up:
"The Microsoft Access database engine does not recognize 'S1.SiteID' as a valid field name or expression."
This is my Crosstab code:
TRANSFORM Sum(H.Difference) AS SumOfDifference
SELECT H.SiteID
FROM (SELECT S1.SiteID, S1.SiteHistoryAsOfDate, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S1.SiteHistoryID)))
ORDER BY S1.SiteID, S1.SiteHistoryAsOfDate DESC) AS H
WHERE (((H.SiteHistoryAsOfDate)>=Date()-8))
GROUP BY H.SiteID
PIVOT H.SiteHistoryAsOfDate;
If I copy the subquery code and run it as a standalone query, there is no error.
How do I modify my crosstab code to avoid the error?
Glenn
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, May 5, 2016 10:16 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] calculating net change
Glenn-
I can't see what Access responded because you can't include attachments in posts in this group.
So, when you get multiple readings on the same date, what do you want to do? If you want to keep the readings, then can you sequence them by SiteHistoryID?
You could try:
SELECT S1.SiteID, S1.SiteHistoryAsOfDate, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE ((S1.SiteID = [S2].[SiteID]) AND ((S1.SiteHistoryID <> S2.SiteHistoryID) AND (S1.SiteHistoryAsOfDate = S2.SiteHistoryAsOfDate) AND ((S2.SiteHistoryID = (SELECT MAX(SiteHistoryID) FROM SiteHistory AS S4 WHERE S4.SiteID = S1.SiteID AND S4.SHistoryAsOFDate = S1.SiteHistoryAsOfDate)) OR ((S1.SiteHistoryAsOfDate <> S2.SiteHistoryAsOfDate) AND
((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryAsOfDate < S1.SiteHistoryAsOfDate)))))
ORDER BY S1.SiteID, S1.SiteHistoryAsOfDate DESC
So, if there's another record for the site whose date is the same but has a different history ID, pick the one that has the highest history ID that's less than the current record … OR … if the dates are not the same, then match with the record that has the highest date less than the current record.
Ugly.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On May 5, 2016, at 3:40 PM, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi again John,
I'm not out of the woods yet. Because the data sometimes included multiple values for the same date I am summarizing by date with this query:
SELECT H.SiteID, H.SiteHistoryAsOfDate, Sum(H.Difference) AS SumOfDifference
FROM (SELECT S1.SiteID, S1.SiteHistoryAsOfDate, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S1.SiteHistoryID)))
ORDER BY S1.SiteID, S1.SiteHistoryAsOfDate DESC) AS H
GROUP BY H.SiteID, H.SiteHistoryAsOfDate;
which produces a list of net changes for each date for each site.
Now I would like to display the most recent data for each site and set up this crosstab query:
TRANSFORM Sum(H.Difference) AS SumOfDifference
SELECT H.SiteID
FROM (SELECT S1.SiteID, S1.SiteHistoryAsOfDate, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S1.SiteHistoryID)))
ORDER BY S1.SiteID, S1.SiteHistoryAsOfDate DESC) AS H
WHERE (((H.SiteHistoryAsOfDate)>=Date()-8))
GROUP BY H.SiteID
PIVOT H.SiteHistoryAsOfDate;
using the first query as a subquery.
When I try to view the cross-tab Access reports:
How do I work around this issue?
Glenn
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, May 5, 2016 5:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] calculating net change
Glenn-
Ah, just noticed a bug in the SQL. It should be:
SELECT S1.SiteHistoryAsOfDate, S1.SiteID, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S1.SiteHistoryID)))
ORDER BY S1.SiteID, S1.SiteHistoryAsOfDate;
What that's doing is pulling up two copies of the table side-by-side, then matching rows in the two on the SiteID and the "previous" record based on SiteHistoryAsOfDate. "Find me the row in the second copy that has the same SiteID and the largest date that is less than the date in the first copy."
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)
On May 4, 2016, at 7:20 PM, 'Glenn Lloyd' argeedblu@gmail.com <mailto:argeedblu@gmail.com> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:
Thanks John,
I'm going to have to work with it a bit to get a better understanding of how it is working.
Glenn
From: <mailto:MS_Access_Professionals@yahoogroups.com> MS_Access_Professionals@yahoogroups.com [ <mailto:MS_Access_Professionals@yahoogroups.com> mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, May 4, 2016 10:05 AM
To: <mailto:MS_Access_Professionals@yahoogroups.com> MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] calculating net change
Glenn-
Sort the output so you can more easily verify the result:
SELECT S1.SiteHistoryAsOfDate, S1.SiteID, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S2.SiteHistoryID)))
ORDER BY S1.SiteID, S1.SiteHistoryAsOfDate;
Your attempt to match SiteHistoryAsOfDate with SiteHistoryID clearly should not work - comparing apples and oranges.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)
On May 4, 2016, at 3:56 PM, 'Glenn Lloyd' <mailto:argeedblu@gmail.com> argeedblu@gmail.com [MS_Access_Professionals] < <mailto:MS_Access_Professionals@yahoogroups.com> MS_Access_Professionals@yahoogroups.com> wrote:
Thanks John,
I've been busy on site for a couple of days so this is the first chance I have had to get back to this. When I cut and paste your SQL into a query, the query produces and empty dataset.
As you have noticed, the SiteHistoryID does not correctly reflect the correct chronological sequence. So I modified the query to this:
SELECT S1.SiteHistoryID, S1.SiteID, S1.SiteHistoryCount, S1.SiteHistoryCount-S2.SiteHistoryCount AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX(SiteHistoryID) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryAsOfDate < S2.SiteHistoryASOfDate)));
But the result is still an empty dataset.
Oddly, however, this query
SELECT S1.SiteHistoryAsOfDate, S1.SiteID, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S2.SiteHistoryID)));
does produce a dataset with 134 record. I haven't tested to see if that is the correct number of records but the data produced is obviously incorrect. Here's a sample of the result
SiteHistoryAsOfDate
SiteID
SiteHistoryCount
Difference
17-Apr-16
733
10142
0
28-Apr-16
733
9930
-212
18-Apr-16
733
9560
-582
17-Apr-16
733
9147
-995
21-Apr-16
733
8893
-1249
19-Apr-16
733
9879
-263
16-Apr-16
733
9146
-996
27-Apr-16
733
8654
-1488
20-Apr-16
733
8607
-1535
17-Apr-16
769
5411
582
16-Apr-16
769
6504
1675
28-Apr-16
769
5484
655
19-Apr-16
769
4952
123
18-Apr-16
769
6586
1757
17-Apr-16
769
4829
0
24-Apr-16
769
4718
-111
23-Apr-16
769
6005
1176
22-Apr-16
769
4983
154
28-Apr-16
707
20872
-155
25-Apr-16
707
20630
-397
25-Apr-16
707
21027
0
23-Apr-16
707
20714
-313
26-Apr-16
707
20953
-74
18-Apr-16
707
21485
458
16-Apr-16
707
20474
-553
21-Apr-16
707
20437
-590
17-Apr-16
707
20132
-895
22-Apr-16
707
20336
-691
28-Apr-16
707
21375
348
17-Apr-16
707
21195
168
29-Apr-16
742
7595
-435
Glenn
From: <mailto:MS_Access_Professionals@yahoogroups.com> MS_Access_Professionals@yahoogroups.com [ <mailto:MS_Access_Professionals@yahoogroups.com> mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Sunday, May 1, 2016 10:02 AM
To: <mailto:MS_Access_Professionals@yahoogroups.com> MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] calculating net change
Glenn-
You can do that by using two copies of your table and linking them on SiteID and either the previous SiteHistoryID or SiteHistoryAsOfDate. The reason I ask about those two columns is your data is a bit confusing. For SiteID 700, you have:
3467
17-Apr-16
32211
700
3473
16-Apr-16
32060
700
The lower SiteHistoryID has the higher date.
The SQL might look like:
SELECT S1.SiteHistoryID, S1.SiteID, S1.SiteHistoryCount, S1.SiteHistoryCount - S2.SiteHistoryCount AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE S1.SiteID = S2.SiteID
AND S2.SiteHistoryID =
(SELECT MAX(SiteHistoryID) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S2.SiteHistoryID);
If it's the date that makes the difference, than use SiteHistoryAsOfDate instead of SiteHistoryID.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)
On May 1, 2016, at 3:09 PM, 'Glenn Lloyd' <mailto:argeedblu@gmail.com> argeedblu@gmail.com [MS_Access_Professionals] < <mailto:MS_Access_Professionals@yahoogroups.com> MS_Access_Professionals@yahoogroups.com> wrote:
I have data that I want to analyze in terms of daily net change. Data reflecting the current volume is collected on a daily basis from approx. 75 locations. The data provider is unable to collect daily changes but needs this value computed by subtracting the previous reading from the last reading. Some locations have frequently changing daily levels while other have only occasional levels.
I am able to produce a short-term history (7 days) using a crosstab to flatten the date. I use the crosstab as a subquery in which each column is subtracted from the column that represents its predecessor.
The situation is similar to electrical power data where meter readings are periodically collected and consumption is calculated by subtracting the previous reading from the current reading. My data is slightly different because the values may indicate decreases as well as increases but this shouldn't affect the calculation.
Setting up a crosstab to calculate over the long term (as opposed to just the past 7 days) is not really practical. So I am looking for an efficient way to calculate periodic changes for a large number of periods.
Sample Date:
SiteHistoryID
SiteHistoryAsOfDate
SiteHistoryCount
SiteID
3455
17-Apr-16
67035
696
3461
16-Apr-16
67034
696
3463
17-Apr-16
40970
697
3465
17-Apr-16
39186
698
3466
17-Apr-16
33243
699
3467
17-Apr-16
32211
700
3473
16-Apr-16
32060
700
3475
15-Apr-16
27102
701
3481
16-Apr-16
28282
701
3482
17-Apr-16
28000
701
3484
17-Apr-16
25075
702
3487
17-Apr-16
24895
703
3492
16-Apr-16
20506
706
3497
16-Apr-16
20474
707
3519
16-Apr-16
15858
715
3531
16-Apr-16
13706
720
3543
16-Apr-16
10320
728
3560
16-Apr-16
9146
733
3570
16-Apr-16
9270
739
3580
16-Apr-16
7609
742
3629
16-Apr-16
6504
769
3766
16-Oct-12
16417
773
Glenn
[Non-text portions of this message have been removed]
------------------------------------
Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
------------------------------------
------------------------------------
Yahoo Groups Links
Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar