ACCESS FEED: Re: [MS_AccessPros] calculating net change

Glenn-

I have no clue why that error message is popping up. It happens even if I do:

TRANSFORM Sum(H.Difference) AS SumOfDifference

SELECT H.SiteID

FROM qselNetChanges AS H

WHERE (((H.SiteHistoryAsOfDate)>=Date()-8))

GROUP BY H.SiteID

PIVOT H.SiteHistoryAsOfDate;

Bug. The alternative is to turn qselNetChanges into a Make Table and dump the data into a table, then run the Crosstab on the table.

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 7, 2016, at 5:03 PM, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Removing the ORDER BY from the subquery doesn't prevent the unrecognized field error.

As for multiple entries for some days, I am going to modify the data so that the AsOf field reflect the time of day as well as the date. I ran the subquery independently and found that there were still multiple results for some dates. My theory is that having a time value will make it easier to get the end of day reading.

I have just uploaded SiteHistory.mdb and an Excel spreadsheet analyzing the results of running the subquery standalone.

Thanks,

Glenn

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, May 6, 2016 10:16 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] calculating net change

Glenn-

I suspect it doesn't like the ORDER BY in the embedded query. Also, your code as currently written will get a difference for all the records when there are multiple per day with the previous day's entry. You can avoid that, using only the record with the highest ID, by doing:

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.SiteHistoryAsOfDate < S1.SiteHistoryAsOfDate))

AND NOT EXISTS (SELECT SiteHistoryID FROM SiteHistory AS S4 WHERE S4.SiteID = S1.SiteID

AND S4.SiteHistoryAsOfDate = S1.SiteHistoryAsOfDate AND S4.SiteHistoryID > S1.SiteHistoryID))

) AS H

WHERE (((H.SiteHistoryAsOfDate)>=Date()-8))

GROUP BY H.SiteID

PIVOT H.SiteHistoryAsOfDate;

That eliminates the duplicates on the S1 side with regard to SiteID and date, using only the one with the highest 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/

(Paris, France)

On May 6, 2016, at 3:38 PM, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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:

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