Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Query Of A Query - Is This Possible?

Posted by: dw85745 Aug 2 2019, 06:21 PM

Since a query returns a recordset, is it possible to then do a query on that returned recordset ?
If so how?

Posted by: GroverParkGeorge Aug 2 2019, 06:56 PM

Are you thinking of something other than nesting the queries?

SELECT *
FROM Query1
WHERE Query1.PK = 1

Posted by: dw85745 Aug 2 2019, 07:20 PM

GroverParkGeorge Thanks for responding.

QUOTE
Are you thinking of something other than nesting the queries?


YES. This is what I want to do -- if possible.

1) Do a SQL SELECT query with a BETWEEN clause (date type) to obtain a Recordset which is "subset" of the table.
2) Run one or more queries against this subset recordset.

If possible, MAY solve my problem here:
https://www.UtterAccess.com/forum/index.php?showtopic=2054607

Posted by: RJD Aug 2 2019, 08:44 PM

Hi: Yes, this is common practice, using a query within another query. This can be done using another query as the FROM recordset, as a subquery, or as a result of an In clause, or other ways. David offered an example in the other thread.

Perhaps you could "rough out" a query using a query that you think might meet your needs, post the SQL and someone here could understand better what you mean and help you with the approach.

Even better would be a db that contains example data, together with a picture of what the final result should be.

HTH
Joe

Posted by: dw85745 Aug 2 2019, 09:26 PM

RJD: Thanks for responding

I had tried this subquery but Errors on the FROM clause:

CODE
SELECT FIRST(t.fldOpen), MAX(t.fldHigh), MIN(t.fldLow), LAST(t.fldClose)
From (SELECT fldHistDateTime, fldHistOpen AS fldOpen, fldHistHigh AS fldHigh, fldHistLow AS fldLow, fldHistClose AS fldClose
  From Sales1
  WHERE fldHistDateTime BETWEEN [pDateBeg] And [pDateEnd]
  GROUP BY fldHistDateTime ASC) As t;


I recognize FIRST and LAST need to be resolved with MIN and MAX - if possible - using PhilS suggestion in the other thread.
AS an alternative I thought I could create a Temporary Table in the DB of the BETWEEN records and then run a query or
several (if need to do MIN and MAX separately) against that Temporary Table but will be slow since I'm accessing a DB
instead of Memory. SO thought if I could run a query against a query (whose recordset is already in memory) it will
be much faster.

FWIW I used GROUP BY instead of ORDER BY in the subquery as it is my understanding subqueries cannot have ORDER BY in them.

Posted by: dale.fye Aug 3 2019, 06:55 AM

Your problem is where you put the ") as T"

Instead of:

CODE
SELECT FIRST(t.fldOpen), MAX(t.fldHigh), MIN(t.fldLow), LAST(t.fldClose)
From (SELECT fldHistDateTime, fldHistOpen AS fldOpen, fldHistHigh AS fldHigh, fldHistLow AS fldLow, fldHistClose AS fldClose
  From Sales1
  WHERE fldHistDateTime BETWEEN [pDateBeg] And [pDateEnd]
  GROUP BY fldHistDateTime ASC) As t;

Use this:
CODE
SELECT t.fldHistDateTime, FIRST(t.fldOpen), MAX(t.fldHigh), MIN(t.fldLow), LAST(t.fldClose)
From (
    SELECT fldHistDateTime, fldHistOpen AS fldOpen, fldHistHigh AS fldHigh, fldHistLow AS fldLow, fldHistClose AS fldClose
    FROM Sales1
    WHERE fldHistDateTime BETWEEN [pDateBeg] And [pDateEnd]
) As t
GROUP BY t.fldHistDateTime ASC

Note: If you are going to group on the HistDateTime field, you should also put that into the outer SELECT statement

Posted by: GroverParkGeorge Aug 3 2019, 07:36 AM

I have not heard that you can't use an ORDER BY clause in a subquery. Perhaps if you can post a reference to the source of that information, I can learn something (always a good thing). Often the context of such advice makes a difference, so I'd like to read it in context. Thanks.

You know already, I think, that First() and Last() are not reliable in most situations because they both depend on the ORDER BY clause applied to the recordset at any given time.

Min() and Max() are usually more reliable because they are not dependent on sort order determined by ORDER BY clause. That's not to say that First() and Last() don't have a place. I suppose they can. It is to say that most of the time, the safer alternative is Min() and Max().

Posted by: RJD Aug 3 2019, 07:40 AM

Hi: I don't think we are going to be able to sort this out for you without a db with a table and some example records, and a display of what you expect as the result from those records. Could you provide that? This would be, I think, essential in helping us help you.

Your comments about not using ORDER BY in a subquery are not correct, and when you added the GROUP BY you included ASC, which is not correct syntax, and you did not provide any Total selections for the fields in the subquery when you used GROUP BY. Thus the errors, our confusion and need for clarification.

HTH
Joe

Posted by: dw85745 Aug 3 2019, 08:56 AM

Thanks all for responding.

Have a lot to go over today with your posts.
A quick comment:
-----------
dale.fry:
-----------
Noticed I had ASC as part of GROUP BY as you also did in your rework of my code.
That was my error as ASC is only used with ORDER BY.
Have not evaluated the rest.

RJD thanks for pointing this out.

-------------------
GroverParkGeorge
------------------------
See my last comments to your post in: https://www.UtterAccess.com/forum/index.php?showtopic=2054607

---------
RJD
--------
See what I can do. Never posted an image to the forum so will have to research how.
Here's it in hard form as an example (tried both code and quote and code gave best formatting).

CODE
The table would look like this
(primary key)
fldHistDateTime     fldHistOpen    fldHistHigh    fldHistLow    fldHistClose     '<< Field Order

7/26/2019 12:54:00 PM    3023.5    3024    3023.5    3023.75
7/26/2019 12:54:00 PM    3023.75    3023.75    3023.5    3023.5
7/26/2019 12:55:00 PM    3023.5    3023.75    3023.5    3023.75
7/26/2019 12:56:00 PM    3023.5    3024.5    3023.5    3024.5
7/26/2019 12:57:00 PM    3024.25    3024.5    3024    3024.5
7/26/2019 12:57:00 PM    3024.5    3024.75    3024.5    3024.5
7/26/2019 12:58:00 PM    3024.75    3024.75    3024.5    3024.75
7/26/2019 12:59:00 PM    3025    3025    3024.75    3024.75
7/26/2019 1:00:00 PM    3025    3025    3024.5    3024.75
7/26/2019 1:00:00 PM    3025    3025    3024.5    3024.5
No Gap - put here for easy of reading groups
7/30/2019 8:47:00 AM    3008.5    3009    3007.5    3007.75
7/30/2019 8:48:00 AM    3007.75    3008.5    3007.5    3008
7/30/2019 8:49:00 AM    3007.75    3009.75    3007.5    3009.25
7/30/2019 8:50:00 AM    3009.5    3010    3007.75    3008.5
7/30/2019 8:51:00 AM    3008.25    3009.25    3007.75    3009.25
7/30/2019 8:52:00 AM    3009    3010.5    3008.5    3010.5
7/30/2019 8:53:00 AM    3010.5    3010.5    3009.75    3010.25
7/30/2019 8:54:00 AM    3010.25    3010.5    3009.75    3010.5
7/30/2019 8:55:00 AM    3010.5    3010.75    3009.75    3010.75
7/30/2019 8:56:00 AM    3010.75    3011.25    3010.5    3011
7/30/2019 8:57:00 AM    3011    3011.75    3011    3011.5
7/30/2019 8:58:00 AM    3011.5    3011.75    3010.5    3011
7/30/2019 8:59:00 AM    3011    3011    3010.5    3010.5

-----------------
Result Set I am After
'Don't worry about the fldHistDateTime time stamp as using the Oldest Date in the BETWEEN clause
'to identify each Group of data
fldHistOpen = oldest date and record value for this field in the group
fldHistHigh = MAX value in the group
fldHistLow = MIN value in the group
fldHistClose = most current date and record value for this field in the group.
---------------------------
fldHistDateTime     fldHistOpen    fldHistHigh    fldHistLow    fldHistClose     '<< Query Output

7/26/2019 12:00:00 PM    3023.5    3025       3023.5         3024.5
7/30/2019 8:00:00 AM    3008.5    3011.75       3007.5         3010.5

Posted by: dw85745 Aug 3 2019, 09:24 AM

-------------
RJD
----------
Here's a link to one of many talking about NOT using ORDER BY in a subquery.

https://www.w3resource.com/SQL/subqueries/understanding-SQL-subqueries.php
For some reason link comes up NOT found, so here from my browser address line

QUOTE
www.w3resource.com/SQL/subqueries/understanding-SQL-subqueries.php


Even quote URL does not work to get you there.
From the page:
QUOTE
Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added into a subquery. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be the last clause.




Another, but Kinda poor example debating the issue.
https://stackoverflow.com/questions/2101908/is-order-by-clause-allowed-in-a-subquery

Posted by: dw85745 Aug 3 2019, 11:33 AM

Trying to solve the query is my primary objective BUT out of that a number of questions have arisen
and the subject of this thread "Query of A Query".

In this regard I was NOT thinking of a subquery as part of the primary query.
BUT the bigger question of what is really going on behind the scenes and
whether I can use that information to do whatever.
My understanding -- if correct:

1) JET (or some other database) holds the data which resides on disk.
2) SQL is a language which allows one to interface and obtain data from the database.
3) Access - which is both a GUI and has some type of scripting/language capability -,
lets one view a database (in this case JET) and as part of that GUI
implementation allow one to execute SQL code.
4) No matter whether one executes SQL using Access, VB, or some other language or method
a result set is returned. My presumption is that this result set resides in memory and how that
result set is presented to the user is dependent on from where it was executed and the code instructions
on how one wishes to display/process that result set.
In Access's case, if one executes a Query within the Access Query Tab
the result set is presented in display form as a recordset.
5) If my presumption is correct, any SQL result set exists in memory.
6) The question then becomes one of whether that result set in memory from the first query can be used as data for use in a second or another query?
For example:
Database Recordset contains data from 1/1/2018 to 12/31/2018
First Query return data for period: 6/1/2018 to 6/30/2018
Second Query now would use the First Query return data recordset as input.

Posted by: RJD Aug 3 2019, 12:02 PM

Hmmm... Just to make sure where we are with this, I created a demo of two queries with subqueries. The table contains a field with C, B, F, A, G, D, E, one alpha character per record for 7 records. One subquery uses ASC sort, and the main query selects the resulting field (without a sort) - and the order is as done in the subquery, A-G. The other subquery uses DESC sort, and the main query selects the resulting field (again, without a sort) - and the order is reversed, as done in the subquery, G-A. Ordering is NOT done in the main query, but in the subquery alone.

To test this further, these two queries were copied and Top 2 added to the main query. In the ASC subquery query, this produces A, B. In the DESC subquery query, this produces G, F. Again, with no sorting in the main query.

So, in fact, the ORDER BY in the subquery does work, except perhaps in the case of First and Last used without grouping.

But the First and Last functions are of value, IMO, only in very specific circumstances.

I'll take a look at your posted data and desired results. It would have been much easier if you could have posted a db with the table, but I'll see what I can do. In any case, my first glance at the table seems to indicate the need for a value associated with the Min and Max time of the group rather than the First or Last of the values. But as I said, I'll see what I can do with this.

HTH
Joe

 SubqueryOrderBy.zip ( 18.94K ): 3
 

Posted by: RJD Aug 3 2019, 01:30 PM

Okay, here's what I found, and what I did.

You had multiple same dates and times. Without any other indications it is not possible to know which record comes first. This impacts the first two records in your post (among others). So, I added an Autonumber ID and entered the records in the same order you posted them. This ID is used later to differentiate the records and provide proper ordering for the first and last records of the date/hour group (NOT First and Last functions, which are not used at all in this solution - as inappropriate).

Your Hour groups include the next hour with 00 minutes. That is, 1:00 PM is included in the hour 12 group. This is achieved by subtracting 1 second from the fldHistDateTime, since you are not using seconds in your DateTime value.

To achieve the results you wanted ...

Extract the HistDate and HistHour values (adjusting the hour by -1 second) So that proper grouping can be achieved.
Sequence records within Date and Hour groups, by combining the datetime and ID.
Identify the first and last sequence numbers within the datehour groups. Name them "First" and "Last" - again, NOT the grouping functions. This uses a separate query identifying the min and max order numbers and then comparing these against the overall order to determine first and last.
Display the values in a Totals (Group By) query (grouping on Date and Hour), using "First" and "Last" text indicators within logic (see query). Then use the Min and Max for the other two values.

See the demo attached, using your posted table. See if it does what you want.

Look at the final results query, then you can track back to see how this is built up, as a query stack, with no subqueries (although these could be combined to use the subquery approach - I just thought it would be easier to follow separately) and no First or Last functions.

You (or others) may be able to simplify this process, but when I came to a solution that seemed to work, I stopped there.

HTH
Joe

 UA_20190803_dw85745.zip ( 21.15K ): 3
 

Posted by: GroverParkGeorge Aug 3 2019, 01:34 PM

I see. Thanks for the references.

We're bumping into a semantic question here.

First, "can" in this context does not refer to the syntax itself. It is certainly possible to write SQL like this and get "a" result.

SQL
SELECT tblA.PK, tblA.*,
FROM tblA INNER JOIN (SELECT tblB .*
FROM tblB ORDER BY tblB.DateField) AS Subquery ON tblA.PK = Subquery.FK


The result may or may not be sorted in a meaningful way, though. And that's a different way of saying "You can't apply the ORDER BY in a subquery."

In other words, the question is not whether the rules permit it, but whether the results will be appropriate.

Second, there's a subtle difference between a definite article "the" and an indefinite article "a".

You CAN apply "a" Sort Order in a subquery, but it may or may not be effective as "the" Sort Order for the resulting recordset.

I know that's probably a bit pedantic. Still it's probably worth it to be sure we're all on the same page.

See Joe's specific comments in which he's explaining the practical impact of these considerations here.


Posted by: dw85745 Aug 3 2019, 02:06 PM

Thanks to you both for your responses.

Need to take a break for a bit, so will digest last two comment, test RJD sample code and post back -- most likely tomorrow (Sunday) rather than today.
In regard to my post timed 12:33PM been thinking a bit about it -- will do more research later - but most likely when they designed SQL
(that raises another question as to whether SQL is a script language or not), they were concerned about the different OSes on which it would run,
and the fact those OSes needed to recapture memory at certain points; Hence since that memory might disappear -- unknown to SQL - the subquery
construct as part of the main query was designed to make sure that the memory allocated would still exist. I'm guessing the SQL designers
felt that one could dump the resulting recordset into an array or some other structure to take advantage of memory, if needed.

Posted by: RJD Aug 3 2019, 02:07 PM

Hi again: I just looked back at the title line of this thread, and noticed that you indicated Access 97 as the version. Obviously, what I posted will not open in A97 as it was created in A2010. So, if it will help, here are the queries used in my last posted solution ...

qryDataPrep1

SELECT Sales1.ID, Sales1.fldHistDateTime, DateValue([fldHistDateTime]) AS HistDate, Hour(DateAdd("s",-1,[fldHistDateTime])) AS HistHour, CLng(DCount("*","[Sales1]","DateValue(fldHistDateTime)=#" & DateValue([fldHistDateTime]) & "# And Format([fldHistDateTime],'yyyymmddhhnn') & Format(ID,'000000')<='" & Format([fldHistDateTime],'yyyymmddhhnn') & Format([ID],'000000') & "'")) AS GroupOrder, Sales1.fldHistOpen, Sales1.fldHistHigh, Sales1.fldHistLow, Sales1.fldHistClose
FROM Sales1
ORDER BY Sales1.ID;

qryDataPrep2

SELECT qryDataPrep1.ID, qryDataPrep1.fldHistDateTime, qryDataPrep1.HistDate, qryDataPrep1.HistHour, qryDataPrep1.GroupOrder, IIf([GroupOrder]=[FirstOrder],"First",IIf([GroupOrder]=[LastOrder],"Last","")) AS OrderPosition, qryDataPrep1.fldHistOpen, qryDataPrep1.fldHistHigh, qryDataPrep1.fldHistLow, qryDataPrep1.fldHistClose
FROM qryDataPrep1 INNER JOIN qryGroupOrderRange ON qryDataPrep1.HistDate = qryGroupOrderRange.HistDate
ORDER BY qryDataPrep1.HistDate, qryDataPrep1.HistHour, qryDataPrep1.GroupOrder;

qryGroupOrderRange

SELECT qryDataPrep1.HistDate, Min(qryDataPrep1.GroupOrder) AS FirstOrder, Max(qryDataPrep1.GroupOrder) AS LastOrder
FROM qryDataPrep1
GROUP BY qryDataPrep1.HistDate;

qryFinalResults

SELECT qryDataPrep2.HistDate, qryDataPrep2.HistHour, Max(IIf([OrderPosition]="First",[fldHistOpen],Null)) AS HistOpen, Max(qryDataPrep2.fldHistHigh) AS HistHigh, Min(qryDataPrep2.fldHistLow) AS HistLow, Max(IIf([OrderPosition]="Last",[fldHistClose],Null)) AS HistClose
FROM qryDataPrep2
GROUP BY qryDataPrep2.HistDate, qryDataPrep2.HistHour;

All this against your posted table, named Sales1, but with ID added to provide initial sequence of records.

See if this will help you accommodate my solution in your version of Access.

If not, perhaps someone else has the software to convert my A2010 db back to A97 - and see if it works there. I'd have to dig out and test some old computers to see if I could do this - something I would not look forward to.

HTH
Joe

Posted by: dw85745 Aug 4 2019, 08:46 AM

--------------------------------------------------------
RJD: Thanks for all your effort on my behalf.
RE: last post stamped 03:07 PM
---------------------------------------------------------

1) Per thread title, I see from your response one can do a Query of Query.
Good to know info as never seen that done and my searches yielded nothing.

2) Regarding the 4 SQL queries provided Will test in a bit, but looking at queries, seems like a lot of work to get such simple information.
Will try and time this once I get up and going and then compare to
PhilS's SQL suggestion.
I also would think doing a SELECT with a BETWEEN and ORDER BY clause with DAO GetRows, and then
evaluating the returned recordset (now in an array) would be a lot faster -- but you never know.

Will post back when done.

---------------------------
GroverParkGeorge
--------------------------
Comments noted.

Posted by: cheekybuddha Aug 4 2019, 09:39 AM

In SQL Server:

CODE
SELECT
  t.Contact
FROM (
  SELECT
    c.Firstname + ' ' + c.Surname AS Contact
  FROM Contacts c
  ORDER BY c.Surname DESC
) t
;

returns:
CODE
SQL Error [1033] [S0001]: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.


Just FYI!

d

Posted by: GroverParkGeorge Aug 4 2019, 10:11 AM

"... unless TOP, OFFSET or FOR XML is also specified."

So this SQL returns records just fine.

SQL
SELECT *
FROM access.tblMealItem MI
INNER JOIN (SELECT top 100 *
FROM ACCESS.tblFoodItem FI
ORDER BY fi.FoodItem)
AS FI ON MI.FoodItemID=FI.FoodItemID



And I've found that it's pointless anyway, as noted in the foregoing discussions regarding "a" sort order being applied in a subquery as opposed to the "the" desired sort order being applied in the outer query.

And don't forget Access SQL <> T-SQL.

Posted by: cheekybuddha Aug 4 2019, 11:24 AM

You probably want TOP 100% with that! wink.gif

I was just trying to explain where the OP may have heard that you can't ORDER BY in a sub-query.

thumbup.gif

d

Posted by: GroverParkGeorge Aug 4 2019, 01:52 PM

Bad copying. Bad.

Yes, missed out the pct. Thanks for catching it.

Posted by: dale.fye Aug 5 2019, 05:51 AM


Where do the time values come from in your desired results, they are not displayed in the sample data you provided?

fldHistDateTime fldHistOpen fldHistHigh fldHistLow fldHistClose

7/26/2019 12:00:00 PM 3023.5 3025 3023.5 3024.5
7/30/2019 8:00:00 AM 3008.5 3011.75 3007.5 3010.5

Posted by: dw85745 Aug 5 2019, 04:21 PM

-------------
dale.fye
-------------
Quick post to answer your question while working on RJD Queries.

QUOTE
Where do the time values come from in your desired results


From the dates entered into the BETWEEN clause.
In the example I used the earliest date in the BETWEEN to identify the specific Group.


Posted by: dw85745 Aug 7 2019, 06:05 PM

Came across something unique while putting this all together and wondered if anyone else has encountered this:
Re: Access97 > Run Query from Query Builder.

WHERE BETWEEN construct:
Prompts for pBegDate and pEndDate one each

CODE
PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime;
SELECT *
FROM Test
WHERE ((Test.fldHistDateTime) Between [pBegDate] And [pEndDate]);


WHERE AND construct:
Prompts for pBegDate and pEndDate two times each

CODE
PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime;
SELECT *
FROM Test
WHERE (Test.fldHistDateTime >= [pDateBeg] And Test.fldHistDateTime < [pDateEnd]);



Posted by: RJD Aug 7 2019, 06:20 PM

CODE
PARAMETERS [pBegDate] DateTime, [pEndDate] DateTime;
SELECT *
FROM Test
WHERE (Test.fldHistDateTime >= [pDateBeg] And Test.fldHistDateTime < [pDateEnd]);

Look carefully at the parameters and WHERE - they are spelled differently e.g. pBegDate vs pDateBeg ...

HTH
Joe

Posted by: strive4peace Aug 7 2019, 07:24 PM

hi dw85745 (what is your name?)

> "do a query on that returned recordset ?"

In VBA, you could dimension a database object variable, and then 2 recordset vartiables ... one for the return of all records, and the second for a filtered recordset. But having data in VBA may not be what you want

If it is, since you already have a saved query, you can also do something like this:

CODE
dim db as dao.database, rs as dao.recordset
    set db = currentdb
    dim sSQL as string
    sSQL  = "SELECT q.* FROM MyQueryname As q WHERE mycriteria"  'might also be the .SQL of a saved query that has been modified
    set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)


BUT ... How are you wanting to use this? To show results on a datasheet or report?

if datasheet , maybe just make a temporary query, based on adding filters to your permanent query. If you get the SQL statement, you can modify it to (1) add a WHERE clause, or (2) modify the WHERE clause

then you can send the (new, temporary) queryname and (changed) SQL statement to a procedure like "MakeMyQuery".

MakeMyQuery(sQueryname, sSQL)
https://www.UtterAccess.com/wiki/MakeMyQuery

Where is the criteria you want to limit by coming from? A form? If so, you could have a command button with a click event that adds the desired criteria, and opens the datasheet view of the new temporary query.

~~~
Optionally, instead of modifying the SQL, you could simply make a new query, using the base query showing all records as a source, and specify criteria for limiting records. Again, you could use the
MakeMyQuery procedure and then just open it.

Or maybe the query is used as the source for a report? That's great! Just overwrite the temporary query that the report is based on, and then open the report.


~~~
note:
If you have a grouped query, often criteria needs to be in WHERE and not in the HAVING clause -- this is also better for performance.

~~~

You can use a parameter query, to prompt for parameters, which is great if you don't need to run a bunch of queries (or reports) with the same criteria ... if you do, though, it is bettter to capture crtieria on a form and then open data the user wants to see.

~~~

> " Run one or more queries against this subset recordset."

Maybe, after collecting criteria, you might save values in TempVars (temporary variables) or database properties (set and get using VBA), and use the same criteria again. Problem with allowing for all criteria, though, is that all may not be filled. It is good to apply just what is filled and ignore what isn't. So better to test what is specified and construct a WHERE clause accordingly. Helps to know what you want to do with the filtered records.

~~~
glanced at link in your siggy ...
First and Last may not be what you think ... perhaps what you really need is Min and Max -- George said that too.

~~~

> "Result Set I am After .. fldHistOpen = oldest, fldHistHigh = MAX, fldHistLow = MIN, fldHistClose = latest (Max) date "

these results you want are on different records. Perhaps you just want statistics?

~~~
How are you wanting to use this? What criteria changes?

I have not read all the messages here in detail -- but hopefully something in these comments is helpful! If I can help you any more, please do your best to answer the questions (sample, as others have noted, help too), thanks ~



Posted by: RJD Aug 7 2019, 07:37 PM

Hi Crystal: Take a look at Post #13, and the attachment solution, using queries. See what you think. I indicated that perhaps this could be a bit simplified (I haven't gone back to look at that aspect, since the demo works ad seems efficient as-is), but look at the methodology for getting the first and last record values using the transaction date/time with the ID as a differentiator. The differentiator is necessary since sometimes the transaction date/time is replicated and the position in the table (see ID) breaks the tie, and this is necessary when the replication is in the first or last positions.

I noticed that no one has bothered to download the demo, so would appreciate a look. It does seem to work without issues, and it does so quickly.

Regards,
Joe

Posted by: strive4peace Aug 7 2019, 07:43 PM

hi Joe,

I would download the demo if I had a clear understanding of the problem ... perhaps, though, you've somehow gleaned what he really wants ... I didn't ... but then I'm coming into this not knowing what's already been asked and done. Do appreciate the extra step you take!!!

Posted by: RJD Aug 7 2019, 07:50 PM

Hi Crystal: I think the OP got to the crux of the matter in Post #9 with the data list and the required result. That's what I worked from. Many (most?) of the OP's other comments/questions/etc. seem to revolve around parts of a perceived solution rather than a clear definition of the data and the result requirement - which got clarified in Post #9. Anyway, that's my interpretation of this so far ... and what my demo was predicated upon.

Regards,
Joe

Posted by: dw85745 Aug 7 2019, 08:18 PM

RJD and strive4peace: Thanks for responding.

------
RJD:
------

Never noticed that.
Almost always use pDateBeg or pDateEnd.
Will correct.

-----------------
strive4peace
-----------------
My name is David.

Here's a simplified query by PhilS (see: https://www.UtterAccess.com/forum/index.php?showtopic=2054607)
I added in the fldHistOpen with this code (Note no syntax error in following between parameters and SQL):

CODE
   'See CRITICAL NOTE on how to make this Query
   strParam = "PARAMETERS [pDateBeg] DATETIME, [pDateEnd] DATETIME; "
   strSQL = strParam
   strSQL = strSQL & "SELECT (SELECT fldHistOpen"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE fldHistDateTime = (SELECT MIN(fldHistDateTime)"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd])"
   strSQL = strSQL & " ) AS fldOpen, (SELECT fldHistClose"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE fldHistDateTime = (SELECT MAX(fldHistDateTime)"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd])"
   strSQL = strSQL & " ) AS fldClose, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd];"


However have tried both using WHERE BETWEEN and WHERE AND constucts and Not getting the correct value for fldHistOpen.
Normally returns either:
1) the fldHistClose value as the fldHistOpen for the record with the correct DateTime (fldHistDateTime)
-or-
2) the fldHistOpen value for the record following the one with the correct DateTime (fldHistDateTime).

Just ran an output and CDbl values (because of precission) seem to vary for the same record stamped 8:30:00 AM but all show 8:30:00 AM when CDate is used.
I think this is what is messing up both the WHERE BETWEEN and WHERE AND output.

CODE
Private Sub Form_Load()

Dim dblIn As Double


Dim dtmDateIn As Date
Dim dtmDateOut As Date

'Get the double value for the following date on my system
dblIn = CDbl(#7/31/2019 8:30:00 AM#)    
dtmDateIn = CDate(43677.3541666667)   '<<Hard Coded value returned from dblIn in line above rather than using the variable.

'Get Date values from the Access fldHistDateTime which I had Access output as doubles with a Query
'Precision is different but Access shows both dates as 7/31/2019 8:30:00 AM
dtmDateIn = CDate(43677.3541667)        '
dtmDateOut = CDate(43677.354166718)


End Sub


Heres a snapshot of the input and output tables I've using for dtmDateIn and dtmDateOut above.



As you can see from the attachments and look at the 8:30:00 AM date lines, the output value for fldHistOpen is wrong when compared to the fldHistOpen input table for 8:30:00 AM.
Access took the 8:30:00 AM values and put them as 6:30:00 AM as output.
I've tried subtracting both a second and a minute from 8:30:00 to get Access to interpret 8:30:00 AM double value as belonging with the 8:30:00 AM grouping but
yet to succeed as fldHistOpen value is always wrong.

Posted by: dw85745 Aug 8 2019, 06:57 AM

While the posted (#30) query seems very logical and well constructed (IMHO),
and also less code and more simple than RJD multiple query,
I am convinced Access (at least Access 97) does NOT like this query - period.
Whether -- because of multiple Subqueries -- is unknown.
Just doing a simple query for:

fldHistDateTime =

or

looking for Min(fldHistDateTime) using a WHERE BETWEEN

finds the correct record (e.g. test case 7/31/2019 8:30:00 AM).
So unless someone can offer a solution to this particular query, going to get back to
trying to time RJD's query, DAO GetRows method, and another idea I have for efficient rollup.

Posted by: dw85745 Aug 10 2019, 09:17 PM

Well I don't get this "Query of A Query" at least as far as how to implement from VB (5/6 in my case)

Ignoring RJD's queries for a moment which IMHO are a little complex, I created
two simple Query calling a Query in Access in the Query Builder.

TestA:

CODE
SELECT *
FROM sale1;


TestB which was manually executed - by me - after TestA executed fine and gave the correct results (recordset).
QUOTE
SELECT TestA.fldHistDateTime, CDbl(TestA.fldHistDateTime)
FROM TestA;


====================
I then tried to do the same from VB (5/6) using DAO.
The first query naturally executes fine.
The problem is how to reference the second query in the DAO.OpenRecordset Method.
This is my latest attempt:

CODE
   'Create SQL string for first Query
   strSQL = strSQL & "SELECT fldHistDateTime, fldHistOpen, fldHistHigh, fldHistLow, fldHistClose"
   strSQL = strSQL & " FROM " & strSrcTBLName
   strSQL = strSQL & " WHERE ((fldHistDateTime >= #" & dteDateBeg & "#) And (fldHistDateTime < #" & dteDateEnd & "#))"
   strSQL = strSQL & " ORDER BY fldHistDateTime ASC;"
  
  'Save, Execute Query, and Get Recordset  
  Set qd1 = DaoDb.CreateQueryDef("qry1", strSQL)    '<<Stored Query, Non Stored also did NOT work.
  Set rsQuery = qd1.OpenRecordset()

   'Create Second Query SQL string that references the first Query recordset
   strSQL = "SELECT rsQuery.fldHistOpen"
   strSQL = strSQL & " FROM rsQuery"               '<<<< Fails here because NOT a Table
   strSQL = strSQL & " WHERE rsQuery.fldHistDateTime = (SELECT MIN(rsQuery.fldHistDateTime)"
   strSQL = strSQL & " FROM rsQuery"
   strSQL = strSQL & " ) AS fldOpen;"
  
   'Execute Second Query and Get Recordset results
   Set rsQuery1 = rsQuery.OpenRecordset(strSQL)

Posted by: isladogs Aug 11 2019, 01:22 AM

Hi David
I won't get involved in the details of your query/subquery as others are providing excellent support.
However you might find it useful to read the articles by Allen Browne about subqueries and their limitations
http://allenbrowne.com/subquery-01.html, http://allenbrowne.com/subquery-01.html

Posted by: tina t Aug 11 2019, 01:15 PM

QUOTE
what I posted will not open in A97 as it was created in A2010

@ Joe
hi, Joe! :) if A2010 can convert the db down to A2003, then i can convert the A2003 version down to A97, if you want.

hth
tina

Posted by: RJD Aug 11 2019, 02:24 PM

Hi tina: wavehi.gif Very nice offer on your part. I hope the OP can use the final results. See the attached, reduced to A2003.

Actually, I thought the process was pretty straight-forward, with the steps separated in queries to show how the build-up occurs. A sequence number must be added to the records since there are duplicate date/times and one needs to know which record is first and last (that is added as well, to definitively identify those records in the sequence sets).

All anyone had to do to use the approach was add the ID autonumber to the existing data file (to differentiate same date/time records), limit to the date range desired and run the queries (which can easily be copied from one of my posts).

But the OP having an A97 db now available might encourage at least the testing of this approach. I know it produces the desired results because it focuses on getting the two values associated with the first and last records in each differentiated date/time sets.

I have no way of knowing if this will even run in A97, but assume it probably will, as long as the domain functions in the queries work.

Thanks again notworthy.gif - let's see where this goes from here ...

Regards,
Joe

 UA_20190803_dw85745_A2003.zip ( 24.6K ): 3
 

Posted by: dw85745 Aug 11 2019, 03:05 PM

-------------
isladogs:
------------

QUOTE
Re: Allen Browne

Read his SubQuery writeup several time previously and recently. Obviously doing something wrong or Access97 won't so what I want.
Have thing working using a single query and a VB5/6 rollup implementation.
Just want to see if SQL was faster plus learn a bit more about Access.
One point PhilS made here https://www.UtterAccess.com/forum/index.php?showtopic=2054607 was to watch out
for FIRST and LAST and use MIN and MAX instead. I totally agree with this point.
Originally (prior to this thread) had -- what I thought -- was a good SQL working solution using FIRST and LAST until PhilS pointed out the deficiency.
A simple followon solution would have been to retrieve either the entire data table or a subset thereof using ORDER BY date
and then run a second query against that returned recordset using FIRST and LAST or even MIN and MAX since the records are
Now in date order because of the first query. One can accomplish this by saving the recordset to a DB temporary table and then
querying that Temporary Table. However, disk access is slow even with SQL. So doing it all in memory is the best way.
HOWEVER doing it in memory (RJD's code) -- from other than the Access Query Builder - seems to be a problem.
Whether that is because of COM, VB, or Access 97?

--------
tina t
--------
Thank you for your efforts on my behalf.. Nice to see your still around.

------
RJD
------
See comment to isladogs.
QUOTE
I thought the process was pretty straight-forward, with the steps separated in queries to show how the build-up occurs


The KEY here is whether or NOT it will run from other than the Access Query Builder.
If one can accomplish that, than any series of multiple queries can be strung together to do whatever and test efficiency.
So far, per my 10:17 PM post (which they showed the post number during editing),
I cannot get a Query to call a Query outside of the Access Query Builder using VB5/6.

Posted by: RJD Aug 11 2019, 03:28 PM

Hi: Have you just tried to run the queries natively in A97? Just double-click on the query.

Joe

Posted by: tina t Aug 11 2019, 08:12 PM

hello Joe! such a pleasure to be able to do something for you for a change - and for the op, of course.

hi David, how are you? :)

i've attached the zipped A97 db. i only have A2003 on my PC, so i got the following msg prior to conversion:



just remember to check references when you open the A97 db in A97, first time.

hth
tina

 UA_20190803_dw85745_A2003_to_A97.zip ( 9.88K ): 2


btw, the conversion/zip/upload only took a few minutes; sorry it took me so long to get to it, folks, i was doing other things this afternoon.

Posted by: dw85745 Aug 12 2019, 08:52 AM

tina t: Thanks for your effort on my behalf.

QUOTE
hi David, how are you?


Doing fine - other than little frustrated over this Query.
Trust everything is OK with yourself?

=========================
KEY POINT:
=========================
My concern is NOT so much "this particular query" than how I RUN (execute) MULTIPLE DEPENDENT queries
(stored or otherwise using Access) FROM VB5/6.



---------------------------
RE: Uploaded tina t mdb.
--------------------------
1) Double clicked qryDataPrep1 >> Presented Table >> GroupOrder column lists #Error
2) For kicks (with column #Error):
Double clicked qryDataPrep2 >> Received Msgbox:
"The expression is typed incorrectly or is too complex ...."



1) Double

Posted by: RJD Aug 12 2019, 12:51 PM

Hi: Just curious ... given that the GroupOrder contains a Domain function, have you checked the References in A97 to make sure none are marked MISSING? And yes, if the first query errors, following queries using that query field will error as well.

HTH
Joe

Posted by: tina t Aug 12 2019, 01:59 PM

QUOTE
---------------------------
RE: Uploaded tina t mdb.
--------------------------
1) Double clicked qryDataPrep1 >> Presented Table >> GroupOrder column lists #Error
2) For kicks (with column #Error):
Double clicked qryDataPrep2 >> Received Msgbox:
"The expression is typed incorrectly or is too complex ...."

i just downloaded the db i posted from home, to my PC here at work, which runs A97. i opened the db and checked for missing references. none, on my PC. so i opened each query and all worked - no #Error or error msgs, etc. i didn't check the results - sorry, not following the thread that closely - but the queries do function in A97.

David, based on the above, i'm guessing you have one or more missing references, as Joe mentioned checking.

hth
tina

Posted by: dw85745 Aug 12 2019, 03:12 PM

tina t:
Thanks for taking your time to double check your mdb.

QUOTE
i'm guessing you have one or more missing references, as Joe mentioned checking.


Not sure where to start on that as I use Access just as a Backend DB.
No references involved that I know of since No forms or other objects invoked (set) in Access.

========================
Started from scratch this AM and Put together this Simple Query from VB against my Test.mdb. and it worked.
Can't explain why the original test sample did Not work previously.
Will copy Joe's example this evening to my Test.mdb and see what happens.
Since yours is an INDEPENDENT mdb, may be missing a DLL on my Win7 system but No error from Access itself, just the Error Noted
on the Rows in the field (column) output. Can post of picture if needed.

CODE
Private Sub QueryOnQuery_Test()

   #If kDEBUGON Then
       Debug.Print "Begin QueryOnQuery_Test"
   #End If

   On Error GoTo Error_QueryOnQuery_Test
  
   '-----------------
  
   Dim dtmDT As Date
   Dim dblOut As Double
'   Dim strParam As String
   Dim strSQL As String
   Dim qryName As String

  
   'Objects
   Dim qd As QueryDef
   Dim rsQuery As DAO.Recordset

   '*******
   'STARTUP
   '*******
  
   '*****
   'MAIN
   '*****
   '================================================
   ' Execute Previously Saved Access Stored Queries
   'THIS WORKS
GoTo VBCreateStored
   '=================================================
  
   '------
   'Query1
   '------
   qryName = "TestA"
   Set qd = DaoDb.QueryDefs("" & qryName & "")
'   With qd
'      .Parameters("pBegDate").Value = dtmDateBeg
'      .Parameters("pEndDate").Value = dtmLastDate
'   End With

   Set rsQuery = qd.OpenRecordset
  
   dtmDT = rsQuery!fldHistDateTime
    
    '------
   'Query2
   '------
   qryName = "TestB"
   Set qd = DaoDb.QueryDefs("" & qryName & "")
'   With qd
'      .Parameters("pBegDate").Value = dtmDateBeg
'      .Parameters("pEndDate").Value = dtmLastDate
'   End With

   Set rsQuery = qd.OpenRecordset
  
   dblOut = rsQuery!ThisDouble
  
  
VBCreateStored:

   '==============================================
   'Create Access Stored Query From VB and Execute
   'THIS WORKS
GoTo VBCreateUnStored
   '==============================================
   '----------
   'Query1
   '------
   strSQL = "SELECT [fldHistDateTime], CDbl([fldHistDateTime]) As ThisDouble FROM tblTEST;"
  
   Set qd = DaoDb.CreateQueryDef("TestC", strSQL)             '<<Stored

   Set rsQuery = qd.OpenRecordset      '(dbOpenForwardOnly)  '(dbOpenSnapshot)
  
  dtmDT = rsQuery!fldHistDateTime
  dblOut = rsQuery!ThisDouble
  
  
   '----------
   'Query2
   '------
   strSQL = "SELECT [fldHistDateTime], [ThisDouble] FROM TestC;"
  
   Set qd = DaoDb.CreateQueryDef("TestD", strSQL)             '<<Temporary

   Set rsQuery = qd.OpenRecordset
  
  dtmDT = rsQuery!fldHistDateTime
  dblOut = rsQuery!ThisDouble
  
  
  
VBCreateUnStored:

   '=========================================
   ' Execute Access UNStored Queries From VB
   'FAILS On Query2
   '========================================
  
   '----------
   'Query1
   '------
   strSQL = "SELECT [fldHistDateTime], CDbl([fldHistDateTime]) As ThisDouble FROM tblTEST;"
  
   Set qd = DaoDb.CreateQueryDef(vbNullString, strSQL)             '<<Temporary

   Set rsQuery = qd.OpenRecordset
  
   dtmDT = rsQuery!fldHistDateTime
   dblOut = rsQuery!ThisDouble
  
   '-----------------------------------------
   'Query2
   'WILL NOT EXECUTE Query2 as Query1 UnNamed
   '--------------------------------------------

   strSQL = "SELECT [fldHistDateTime], [ThisDouble] FROM TestC;"
  
   Set qd = DaoDb.CreateQueryDef(vbNullString, strSQL)             '<<Temporary

   Set rsQuery = qd.OpenRecordset      '(dbOpenForwardOnly)  '(dbOpenSnapshot)
  
  dtmDT = rsQuery!fldHistDateTime
  dblOut = rsQuery!ThisDouble
  


   '///////
   'WRAPUP
   '///////

   qd.Close
   'Set qd = Nothing
   rsQuery.Close
   'Set rsQuery = Nothing

   Exit Sub

Error_QueryOnQuery_Test:

Dim Number As Long
Dim strErr As String

    Number = Err.Number
   strErr = Err.Description

End Sub

Posted by: tina t Aug 12 2019, 04:44 PM

QUOTE
Not sure where to start on that as I use Access just as a Backend DB.

what Joe uploaded, and then i converted and uploaded, is what i tested today, hon. i opened a new standard module, and from there opened Tools | References, and checked for missing references. and found none, on my PC. then i closed the module without saving it.

you could give the above a try, and see what it shows on your PC.

hth
tina

Posted by: dw85745 Aug 12 2019, 05:12 PM

QUOTE
i opened a new standard module, and from there opened Tools | References


Thanks, found it. MISSING: Microsoft Visual Basic for Applications Extensibilities 5.3
Will compare with my Test.mdb.

Mine does NOT have that reference.
Unchecked it (the MISSING) in yours and Joes queries ran fine.
Interesting that a missing reference in a Module Tab that contains nothing and should have No effect on a query would cause an Error.
Such is Life !!!

==============================================

Did run Joe's Queries on my AM rework of the mdb and they (ran). (Happy Days Are Here Again :>)
Didn't check the output for accuracy, but first glance looked like just gave first and last for entire recordset rather than by Date group.
Slow as molasses though, which I suspected looking at the code previously.
Tried a reduced table size of 121 records only and did OK.
Tried a normal size table for me (around 53000) and could go take an hour nap. (just kidding BUT slow).

If not to much to ask -- and you have the time -- would you mind seeing if you can run PhilS's query
and then check output for fldOpen to see if correct.
Query ran on my system, just fldOpen output Wrong when compared to the source table values.
Will rerun on AM test.mdb to see if still runs and what output looks like.

(DON'T BOTHER -- so far ran fine on my Test System including giving correct output).

Here's the query (copied from Post #30 this thread)
CODE
'See CRITICAL NOTE on how to make this Query
   strParam = "PARAMETERS [pDateBeg] DATETIME, [pDateEnd] DATETIME; "
   strSQL = strParam
   strSQL = strSQL & "SELECT (SELECT fldHistOpen"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE fldHistDateTime = (SELECT MIN(fldHistDateTime)"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd])"
   strSQL = strSQL & " ) AS fldOpen, (SELECT fldHistClose"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE fldHistDateTime = (SELECT MAX(fldHistDateTime)"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd])"
   strSQL = strSQL & " ) AS fldClose, MAX(fldHistHigh) AS fldHigh, MIN(fldHistLow) AS fldLow"
   strSQL = strSQL & " FROM " & strTBLName
   strSQL = strSQL & " WHERE (fldHistDateTime) BETWEEN [pDateBeg] And [pDateEnd];"


David

------------------------------------------------------------------------------------
Don't know if I should make this another thread but will ask anyway
------------------------------------------------------------------------------------
Anyway to ReName a Access Query in code (SQL preferred) using VB5/6?

Posted by: tina t Aug 12 2019, 08:28 PM

QUOTE
Interesting that a missing reference in a Module Tab that contains nothing and should have No effect on a query would cause an Error.

it's because of the domain functions running in the query(s), as Joe mentioned in post #40.

hth
tina

Posted by: RJD Aug 12 2019, 08:52 PM

Hi: Just checking back in ...

Since I had to manually type in all the data you provided, I made one typo on the last record in the 7/26 set. fldHistClose was entered as 3024.75 and should have been 3024.5. This threw off the last number for the summary. With that corrected, the query stack produces exactly the results you posted in Post #9 as required. My error on data entry. Perhaps that is the difference you see. Try changing that one field value in the demo and see if you get the correct results.

"Slow as molasses though ..." We obviously have very different setups. Using the db solution I posted, and using my i5 8GB Dell Inspiron 2-in-1 with Win 10 and Access 2010, and using just the data you posted, the query stack runs in less than 1 second. That's running the final results query, which calls, down the line, all the other queries and the table.

Just thought I'd bring up those items as you continue to evaluate this ...

HTH
Joe

Posted by: dw85745 Aug 12 2019, 09:22 PM

---------
tina t
---------

QUOTE
it's because of the domain functions running in the query(s), as Joe mentioned in post #40.

Thanks for pointing that out -- didn't make the connection as VB5/6 - to my knowledge - does Not use domain functions.

They may be accessible to VB5/6 through an API or VBA.Dll (another thing to check when done with this issue).
Have used them once or twice in Excel -- a rare occurance.

--------
RJD
--------
Appreciate All Your efforts (and all other poster to this thread) on my behalf.

QUOTE
Since I had to manually type in all the data you provided


My Apologies, would have uploaded a CSV file if I'd known.

QUOTE
I made one typo on the last record....
the query stack produces exactly the results you posted in Post #9


Not a biggie. Haven't gone over the output in depth.
Will be more careful in future of what I post.

QUOTE
"Slow as molasses though ..."

Speed comment was NOT a criticism in any way of you or your code.
Just an observation from my system point of view.
I was in Access Query Builder when I manually double clicked qryDataPrep1.
Most likely because it was outputting the results a row at a time to the screen it took a long time to run - by my observation, not any timing code involved yet.
I'm running Win7 with an i5, 8GB and a 4690K so should be fast enough.

Posted by: cheekybuddha Aug 13 2019, 06:36 AM

Is the problem that Acc97 has trouble with sub-queries?

Using the dataset as posted by Joe in https://www.UtterAccess.com/forum/index.php?showtopic=2054631&view=findpost&p=2726294, and corrected as per Joe's https://www.UtterAccess.com/forum/index.php?showtopic=2054631&view=findpost&p=2726413, I could obtain the result desired in David's https://www.UtterAccess.com/forum/index.php?showtopic=2054631&view=findpost&p=2725584 using the following SQL:

CODE
SELECT
  Format(t.fldHistDateTime, "YYYYMMDD HH") AS DateHour,
  FIRST(t.fldHistOpen) AS fldHistOpen,
  MAX(t.fldHistHigh) AS fldHistHigh,
  MIN(t.fldHistLow) AS fldHistLow,
  LAST(t.fldHistClose) AS fldHistClose
FROM (
  SELECT
    *
  FROM Sales1
  ORDER BY
    fldHistDateTime
) t
GROUP BY
  Format(t.fldHistDateTime, "YYYYMMDD HH")
;


What am I missing?

d

Posted by: RJD Aug 13 2019, 01:25 PM

Hi David: Let's see if I can give a good explanation for this. In your demo you are using a clean table (the one I provided), with everything in the correct order. So your approach with First and Last will duplicate the results, in this limited case. However, in fact, the ORDER BY in the query does not affect the First and Last functions - these functions look back to the original records in the table (see earlier discussions on this "disturbing" "feature"). So, if we were to delete record ID 3 in my table, for example (assuming it was originally forgotten ), and add it later (in this case the Autonumber makes it record ID 24), it falls physically as the last record in the date/hour table set, even though it is not the last record in the ordered date/time set.

Take a look at the results of your query now, in the revised demo, having moved the record indicated above - vs my query stack. The Last function value is no longer correct. Whereas, in my approach the "Last" calculated value is still correct. This is because I use the sequencing and tagging approach to get the first and last records from queries, rather than the First and Last functions which look back at the table source.

Yes, I know, this is a bit maddening. But I don't think we can assume the table will be in an order we expect. Yes, you could probably re-write the table to another table, putting the records in the correct sequence for your query. But if the table is not re-written, then the possibility of incorrect results is present (due to unknown ordering in a table of many records) if a sequencing approach is not used.

Also note that I subtracted one second from the time in your query to align the hours per the OP's schema.

This is an interesting exercise, expanding my knowledge and skills in this area. I may not have the simplest and best solution, but it works and I learned a lot. I would be very happy to have a simpler approach, however.

Regards,
Joe

 UA_20190803_dw85745_FirstLastProblemDemo.zip ( 22.61K ): 1
 

Posted by: dw85745 Aug 13 2019, 01:44 PM

-------------------
cheekybuddha
-------------------
Will take a look at your posted code.
See it has FIRST and LAST which was my original implementation that Worked.
However, as PhilS pointed out (see previous posts this thread, link, and my comments) use of FIRST and LAST is Not a good idea.
Hence been rewriting a lot of code to eliminate FIRST and LAST.
Don't get me wrong FIRST and LAST has its place but Not in this case.

---------
RJD
--------
Been putting together the VB App today to test alternatives. Will post results when done.

QUOTE
assume the table will be in an order we expect

That's what I always assumed and hence the use of FIRST and LAST.
However, for production want to make sure .

QUOTE
This is an interesting exercise, expanding my knowledge and skills in this area.

Me also! Lot of different ways to skin a cat, which causes confusion sometimes.

Posted by: dw85745 Aug 13 2019, 07:46 PM

RJD, tina t, and Everyone Else Interested.

FWIW, have not looked at recent post code yet.
Will try and do later this evening.

Finished the Test Program and All Execute fine (PhilS still has errors in output, but executes fine) except RJD code.
The problem I am experiencing RJD is that I am executing the Access queries basically as a batch file from VB5/6.
Only way I know how to execute multiple queries from VB5/6.
-----------------------------
The PRIMARY ISSUE:
-----------------------------
I need some form of a Callback from Access to VB5/6 so VB5/6 knows when qryDataPrep1 is done so it can
proceed to qryDataPrep2, etc. That means I would need to implement a Wait Loop in VB5/6 for each query called.
Here's what I did to execute your queries without Any Wait Loop.

CODE
   '*****
   'MAIN
   '*****
   '------
   'Query1

'????? HOW DO I GET A RETURN CALL TO KNOW Query1 is Done, so VB5/6 can proceed to Query2
   '------
   'Reference Stored Query
   Set qd = DaoDb.QueryDefs("qryDataPrep1")
  
   Command4.Caption = "qryDataPrep1 - Done"
  

   'Process Query
(FWIW Other queries I pass in parameters which define Each Group Size by Date
'   With qd
'      .Parameters("pDateBeg").Value = dtmDateBeg
'      .Parameters("pDateEnd").Value = dtmDateEnd
'   End With
  

   '------
   'Query2
   '------
   'Reference Stored Query
   Set qd = DaoDb.QueryDefs("qryDataPrep2")
  
   Command4.Caption = "qryDataPrep2 - Done"
  
   '------
   'Query3
   '------
   'Reference Stored Query
   Set qd = DaoDb.QueryDefs("qryGroupOrderRange")
  
   Command4.Caption = "qryGroupOrderRange - Done"
    
   '------
   'Query4
   '------
   'Reference Stored Query
   Set qd = DaoDb.QueryDefs("qryFinalResults")
  
   Command4.Caption = "qryFinalResults - Done"
  

   'Return Recordset
'Other queries I return a Recordset of each Group and that use that Recordset to create/update
'the Group Table.  Yours does this all in Access, but the Group RollUp Table needs to be
'identified in order to download the recordset for subsequent use by VB
'   Set MinuteToRollUp_RJD = qd.OpenRecordset()  'dbOpenForwardOnly  (dbOpenSnapshot)

Posted by: RJD Aug 14 2019, 06:21 AM

Hi again: I am confused about how you will capture the results from one query and use them in the next, or, given the query arrangement, how that is even possible. Perhaps i am missing something in how you are using VB5/6.

My thought is that you might consider opening Access from VB with a Macro indicated to run (perhaps AutoExec), with the Macro calling code to export the final results query, say to a CSV file (or whatever available file format you want). This will run the query stack and export the results. Then the results should be available to display, manipulate, etc. as you wish.

Perhaps I missed this, but I do not know what you intend to do with the results from the query stack.

I am not sure about the procedure in A97 as I have not used that version in almost 20 years. Perhaps, if you are interested in pursuing this, others can advise.

In any case, I wonder if using a more current version of Access, and using it directly instead of a BE for a VB5/6 procedure, might be an option for you.

HTH
Joe

Posted by: dw85745 Aug 14 2019, 08:23 AM

----------
RJD
------
Yea, at a point myself where I think I'll go with what I have.

FWIW:
1) Could not use your last posted code as Not an mdb file (again because I'm using Access97)

2) I'm NOT sure how you yourself are executing these multiple queries (DataPrep1 >> FinalResult).
If running them from a Macro, I would think you would run into the same issue as I am with VB.
That is knowing when one Query Finished so that the next could be executed.
To my knowledge there is nothing internal in the OS that does that, hence a code based Wait Loop is required.

3) How I handled my implementation is by date group Let's say I want to know for Each Day of the Last Week,
the First Sale of the Day, the Last Sale of the Day, the Largest Sale of the Day, and the Smallest Sale of the Day.
So since each sale has a Date and Time stamp I would do::

CODE
      Do

         Exit Loop When at .EOF

        (1)
        'Construct a Unstored SQL query in VB or a Have a Stored Query in Access
        Execute the Query and RollUp all Sales for EACH day.
        This would return a Recordset to the calling procedure (VB5/6 in this case)
        with the Daily rollup values (First, Max, Min, Last)

        (2) Either write out the just received Daily RollUp Recordset back to a Another Access Table
             which would hold Daily Values
             -- or -
             Put (save) these Daily RollUp Recordset values to an Structure / Array to be by
            the VB5/6 App when done.

     Loop

     (3)  Now have All Daily RollUp Records in a Structure / Array
           -or- if Put (Saved) to an Access Table then need to read those records from that  Access Table
           and do Whatever. with those records.


There is nothing Wrong in doing this Entire RollUp Process in Access (Using a Daily Table and Creating a Weekly Table)
which can be read by the Calling Program (VB5/6). The only issue is one needs some form of Callback so that the
Calling program knows whent Access is finished, and the Table is available -- OR -- the Caller would have to periodically
check Access (some type of timer routine) to see if the Table existed and had all records. I prefer the callback.

As stated above, this same notification issue exists when running multiple queries. HOW DOES EACH FOLLOWON
QUERY KNOW the the PREVIOUS QUERY is DONE.

(4) While my Original SQL -- which uses FIRST and LAST -- has worked fine for years, based on PhilS excellent white paper,
he pointed out the deficiencies. I did a Test App and agreed. One either has to create and write a Sorted Table (on whatever Key)
prior to using a SQL call containing FIRST and LAST, and even then (my assumpiton here) that one is Not guaranteed that the DB
wrote (saved) those records to the Table in Key order.

Hope this helps.
Thank you and to everyone else for all your efforts on my behalf.
I learned a few things, so a productive several days, and hopefully for yourself and others.

I would appreciate some feedback on how you are handling the call to execute multiple macros in a row
-since as always - there a lot of things I do not know.

David

Posted by: RJD Aug 14 2019, 11:11 AM

Hi: I just wanted to address your first two points ...

QUOTE
1) Could not use your last posted code as Not an mdb file (again because I'm using Access97)

Yes. I posted that for David only, to address the First and Last problem in re his suggested SQL. So I had not anticipated you opening the db, since you were already beyond that issue.

QUOTE
2) I'm NOT sure how you yourself are executing these multiple queries (DataPrep1 >> FinalResult).
If running them from a Macro, I would think you would run into the same issue as I am with VB.
That is knowing when one Query Finished so that the next could be executed.
To my knowledge there is nothing internal in the OS that does that, hence a code based Wait Loop is required.

Nothing special is required in Access to execute a query stack. You simply execute the final query and Access does the rest, calling any source queries and tables required. That's the value of using Access natively. If you go to the db window with the list of objects (Navagation Pane in current versions, I do not remember how it was named in A97), and double-click on qryFinalResults, everything else is taken care of by Access and the results are presented. No Macros, no need to know which queries are required when, no loop, no nothing extra except the native capabilities of Access.

As I said, I cannot see how the VB5/6 procedure could work directly for a query stack. Thus, my suggestion to use Access natively (possibly exporting the query results to an external file) or executing the Access application from VB5/6 using the Macro extension method, where the Macro in Access calls code to export the query results - where you can display or otherwise use the results as you wish. The export of a query's results, and the associated automatic execution of the query stack when you do, is a normal and usual procedure in Access.

HTH
Joe

Posted by: dw85745 Aug 14 2019, 11:18 AM

---------
RJD
--------

QUOTE
(possibly exporting the query results to an external file) or executing the Access application from VB5/6 using the Macro extension method, where the Macro in Access calls code to export the query results - where you can display or otherwise use the results as you wish. The export of a query's results, and the associated automatic execution of the query stack when you do, is a normal and usual procedure in Access.


Any chance you provide of an example of #1 and #2 as how this is done. As I see it:

(1) VB would need to call Access to Execute the Macro
(e.g. How do I call a Macro and Execute it -- like a Table or Query from VB ?)

(2) The Macro would need to notify VB when it is done. (This would be a callback but would require a Wait Loop within VB to know when Access macro was done)
The question here is how Access Macro would sent its notification across Process -- (outside its Process)?

(3) VB would then call Access and get the Table and Associated Data (easily done as already doing).

David

Posted by: RJD Aug 14 2019, 11:38 AM

Hi David: I'll let you do some research...

1. Look up AutoExec macro (I have no idea if this is in A97, but has been common for many years), or using the /x extension when calling an Access file (see 3.).
2. Look up DoCmd.TransferText for sending query results to a CSV file.
3. Look up Open an MS Access file from VB6.

HTH
Joe

Posted by: dw85745 Aug 14 2019, 01:03 PM

RJD - You beat me to the following:

Haven't tried it yet, but normally how one Marshalls across MS Apps.

CODE
Open and run access macro from vb

Dim ObjAccess
Set ObjAccess = CreateObject("Access.application")
ObjAccess.visible = false
ObjAccess.OpenCurrentDatabase("filepath\filename.accdb")
ObjAccess.Run "Test6"                     '<<< NOT SURE WHAT THIS IS AND WHY NEEDED BASED ON NEXT LINE
ObjAccess.DoCmd.RunMacro "Macro1"
ObjAccess.Quit
Set ObjAccess = Nothing


The callback part is the one of most concern.
As CSV would be OK, but a Recordset would IMHO be better based on the little I know at this point about Access GUI and command set.

Posted by: tina t Aug 14 2019, 01:35 PM

QUOTE
1. Look up AutoExec macro (I have no idea if this is in A97, but has been common for many years), or using the /x extension when calling an Access file (see 3.).
2. Look up DoCmd.TransferText for sending query results to a CSV file.
3. Look up Open an MS Access file from VB6.

just fyi:

1. yes, available in A97, i use them daily in a number of dbs.
2. also works in A97.
3. also very do-able, as David's post #57 addresses.

hth
tina

Posted by: RJD Aug 14 2019, 04:34 PM

David: I am still not clear why you couldn't just use Access and not get at the data through VB5/6. All the tools are already there.

In any case, couldn't you just use a Shell command and open Access that way, with the macro extension to the command? I do not get at Access from VB5/6 so you'll have to see if the Shell command will do this.

You could also, instead of exporting the results, make the final query a MakeTable and the end results will then be in table form, if that is what you prefer.

HTH
Joe

Posted by: dw85745 Aug 14 2019, 05:23 PM

QUOTE
I am still not clear why you couldn't just use Access and not get at the data through VB5/6
..... the end results will then be in table form


I have this LARGE App that interfaces with several servers on a Realtime basis.
So manually doing things in Access and then getting at the data through VB5/6 is not an option.
As stated above, if I can Run a Macro from VB that creates a Table in Access that is one leg of the problem.
That appears doable from my limited experience with Office, my Post #57, and tina t's confirmation in the Post above yours.
(FWIW my browser does NOT show post #'s in Edit mode so unable to reference.).

So my purpose here would be to execute a macro against the "base table" and have it produce a "results table".
Per above paragraph should be an easy code.

The Kicker, is how to notify VB5/6 the macro is done, and the table is available for use.
I can't let Access send the table to the VB5/6 App as it may interrupt other processing .
Since VB5/6 normally does NOT support multiple threads (or not to the degree I would like) that puts
the requirements on the Main App to determine when the Table is available,
As stated above, either a Callback to the VB5/6 App from Access to say "I'm here", or a timer which would
poll the Access App checking the table or the macro. While a timer will work it is clock cycle expensive.

Re: Shell. From my use of Shell, it is normally Run and Release. There is no provision I can recall
for a callback, though I have seen some "workarounds" to "try" and do this.
I would have to check my code library but based on past experience -- no.

David

Posted by: RJD Aug 14 2019, 05:38 PM

Okay. Understood. Everything but the callback is within my skill set, and should be easy to do. I'll have to leave the callback, or some alternative, to your devices. The Shell command to open a Macro in Access which runs a MakeTable query (based on my final query) to put the final results in a known table name is straightforward.

Good luck on getting this put together. Let us know how it turns out.

Joe