Sql

[JohnPapa]

I am trying to get a specific output out of the following data with the use of SQL, which is much faster than recordset manipulation. Cannot seem to be able to select the last record of the Group as indicated below.

Thanks in advance,
John

DATA
[COLOR=darkorange]strDescription    strCC          strAC           dtmDate     dtmTime    strPointer[/COLOR] 
AAA                   1             1           7-Apr-11    09:00:00     
AAA                   1             1           7-Apr-11    09:05:00
AAA                   1             1           7-Apr-11    09:07:00
AAA                   1             1           7-Apr-11   11:00:02
AAA                   1             1           7-Apr-11   13:03:00
AAA                   1             1           7-Apr-11   14:05:00
AAA                   1             1           7-Apr-11   17:34:32
AAA                   1             123        7-Apr-11   10:00:00       13
AAA                   1             123        7-Apr-11   11:00:00
AAA                   1             123        7-Apr-11   12:00:00       13
AAA                   1             123        7-Apr-11   15:00:00       18
AAA                   1             123        7-Apr-11   16:00:00
AAA                   1             123        7-Apr-11   17:00:00       18
AAA                   1             177        7-Apr-11   10:00:00       15
AAA                   1             177        7-Apr-11   11:00:00
AAA                   1             177        7-Apr-11   12:00:00       15
AAA                   1             177        7-Apr-11   15:00:00       19
AAA                   1             177        7-Apr-11   16:00:00
[COLOR=red]AAA                   1             177        7-Apr-11   17:00:00[/COLOR]        
AAA                   1             188        7-Apr-11   10:00:00       21
AAA                   1             188        7-Apr-11   11:00:00
AAA                   1             188        7-Apr-11   12:00:00       21
AAA                   1             188        7-Apr-11   15:00:00       22
AAA                   1             188        7-Apr-11   16:00:00
[COLOR=red]AAA                   1             188        7-Apr-11   17:00:00[/COLOR]             
BBB                    2             2           7-Apr-11   14:05:00
BBB                    2             2           7-Apr-11   17:34:32
BBB                    2             228        7-Apr-11   10:00:00      23
BBB                    2             228        7-Apr-11   11:00:00
BBB                    2             228        7-Apr-11   12:00:00

Output required is (indicated in red above)
AAA 1 177 7-Apr-11 17:00:00
AAA 1 188 7-Apr-11 17:00:00

In other words
SELECT WHERE strCC = 1
AND strCC <> strAC (ie Where 1 <> 1)
SORT ASC by dtmDate and dtmTime
GROUP BY strAC (in this case 123, 177, 188)
to output the last record of each GROUP if strPointer = Null

 

[pbaldy]

Does this work for you?

http://www.baldyweb.com/LastValue.htm

 

[JohnPapa]

Many thanks Paul for your reply and for the link. I have been trying to get it to work and have been doing some reading on SQL.

For example, I need to select all records where strCC = 1 AND strAC <>
strCC , Sort DESC dtmDate and dtmTime and output the first record of
every strAC Group where strPointer <> Null


SELECT * FROM tblDP
WHERE (strCC = 1) AND (strAC <> strCC)
ORDER BY strAC DESC, dtmDate DESC, dtmTime DESC


I now need to select the first record (most recent based on dtmDate
and dtmTime) of every strAC group assuming strPointer <> null.


Any ideas?

 

[pbaldy]

Can you post a db with that sample data in it?

 

[JohnPapa]

Hi Paul,

I attached an mdb (Access 03) which contains a table, query and form.

The form contains a description of what is needed.

Regards,
John

 

[pbaldy]

Do these two get the desired result?

qryPaulMax:

SELECT tblDP.strDescription, Max(tblDP.dtmDateTime) AS MaxOfdtmDateTime
FROM tblDP
WHERE tblDP.strCC<>[strAC]
GROUP BY tblDP.strDescription

SELECT tblDP.lngID, tblDP.strDescription, tblDP.strCC, tblDP.strAC, tblDP.dtmDateTime, tblDP.strPointer
FROM tblDP INNER JOIN qryPaulMax ON (tblDP.dtmDateTime = qryPaulMax.MaxOfdtmDateTime) AND (tblDP.strDescription = qryPaulMax.strDescription)
WHERE tblDP.strPointer Is Null

 

[JohnPapa]

Many thanks Paul,

It seems to be OK now. Is there a way to incorporate both SELECT into one statement?

Do we need to create a separate query (qryPaulMax) and store it, so that is used in the second SELECT?

Regards,
John

 

[pbaldy]

I personally feel it's more maintainable as two queries, but it can be one. This is what the queries from my link look like as one:

SELECT VehicleMiles.*
FROM VehicleMiles INNER JOIN (SELECT Max([VehicleMiles].[DorDate]) AS MaxDate, [VehicleMiles].[CarNum]
FROM VehicleMiles
GROUP BY [VehicleMiles].[CarNum]) AS qryMaxDates ON ([VehicleMiles].[DorDate]=[qryMaxDates].[MaxDate]) AND ([VehicleMiles].[CarNum]=[qryMaxDates].[CarNum]);

The query design grid will often change the subquery parentheses into brackets, like:

SELECT VehicleMiles.*
FROM VehicleMiles INNER JOIN [SELECT Max([VehicleMiles].[DorDate]) AS MaxDate, [VehicleMiles].[CarNum]
FROM VehicleMiles
GROUP BY [VehicleMiles].[CarNum]]. AS qryMaxDates ON (VehicleMiles.CarNum = qryMaxDates.CarNum) AND (VehicleMiles.DorDate = qryMaxDates.MaxDate);

 

[JohnPapa]

Paul many thanks for your reply,

Having 2 queries is definitely more maintainable and is the preferred way to go. Do we have to create and store the query that will be invoked (qryPaulMax) or can this be created on the fly? I believe that Access can handle VIEWS in ADO but they are essentially Queries which are stored as Queries.

Any ideas on this.

Regards,
John

 

[pbaldy]

I personally would save the query, as saved queries get compiled and are thus a little more efficient. That said, you can certainly create it on the fly if you prefer. I use SQL Server views a lot, but I haven't used an ADO view.

 

[JohnPapa]

Paul

A couple of issues:
1) If I decide to use 2 different SQL statements as you suggest,
is there a way to create the first SQL on the fly without saving it as a query? I could save and delete, but I was wondering whether I can create the equivalent of a VIEW. Unfortunately the VIEW with ADO merely creates a query.
2) I am trying to use INSERT INTO and would like to have as one of the fields a combination of Date and Time ex 14-Apr-11 18:23:01. If I use just the date then it is fine. It chokes on the combo and says Runtime Error 3075, Syntax error in date in query expression '# 14-Apr-11 18:23:01#' Do you know whether there is a way to INSERT a Date+Time variable?

Thanks,
John

 

[pbaldy]

I'm not sure why you seem to want to avoid saving the query. I don't see it as being any different than a view. You should be able to use a combined date/time value as easily as a date. Can you post a sample that fails?

 

[JohnPapa]

Hi Paul,

I would like to thank you once more for your asistance. I have figured things out and I include below one of the SQL statement that works.

I ran into two problems with saving the query
1) As you can see below the SELECT statement looks up a value on a form ([Forms]![frmRoutingTabular]![cboDP]). When I tried to embed a saved query as you sugggested, it would not recognize the value.

2) Also in the QBE editor I need to use the * to match characters, where in VBA ADO I need to use %. For example to match all entries that begin with string '1234' in the QBE I need to use LIKE '1234*', whereas in VBA ADO I need to use LIKE '1234%'. I can get around this by including in the QBE editor LIKE '1234%', which does no execute correctly in the QBE.

Any ideas on these two issues?
John

strSQL = "SELECT tblRouting.* " _
& " FROM tblRouting INNER JOIN " _
& " (SELECT tblRouting.strAC, Max(tblRouting.dtmDTImplement) AS MaxdtmDTImplement, tblRouting.lngDP " _
& " FROM tblRouting " _
& " GROUP BY tblRouting.strAC, tblRouting.lngDP " _
& " HAVING (((Max(tblRouting.dtmDTImplement)) Is Not Null) And ((tblRouting.lngDP) = " & [Forms]![frmRoutingTabular]![cboDP] & "))  ) AS qry94 " _
& " ON (tblRouting.strAC = qry94.strAC) " _
& " WHERE (qry94.maxdtmDTImplement = tblRouting.dtmDTImplement) AND NOT( (tblRouting.strCC <> tblRouting.strAC)  AND  (tblRouting.lngIndicator =0) ) ;"

 

[pbaldy]

If you're using the query for a recordset, I suspect you're running into this type of thing:

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

One way around it that usually works is to wrap the form reference in the query in the Eval() function:

Eval('[Forms]![frmRoutingTabular]![cboDP]')

As to the wildcards, I don't know that there's a way around it. DAO and JET want the *, ADO wants the %. I guess if you want to stay consistent, you could use DAO instead of ADO, which would let you use the * all the time.

 

[LPurvis]

Declaring the parameter (and it's data type) is usually enough for the value recognition issue. (But Eval works too - it just doesn't feel as "pure" to me, but it feels more so to others - you choose

The wilcard can be made consistent if you are willing to switch operator.
The Alike keyword requires the ANSI standard syntax - i.e. always % as a wildcard.
Therefore using either the Access UI, DAO or ADO your query would be:
WHERE FieldName Alike '1234%'

>> Unfortunately the VIEW with ADO merely creates a query.
That's what a View is in a Jet/ACE database. ;-)
A parameterless query.
Action or parameter queries become part of the Procedures collection in ADOX. They're just lumped in together in the Access UI and DAO OM.

Cheers.

 

[JohnPapa]

Hi Leigh,

Thanks for your post.

With Access/JET can a VIEW be used without creating a query that will be stored in Jet? It seems that I cannot create a VIEW like a virtual table/query without having to store it.

Regards,
John

 

[LPurvis]

I don't quite understand the concept you're reaching for.
How, in SQL Server, would you have a temporary view? They don't exist.
What you do have are derived tables, subqueries. i.e. inline SQL statements.
That's what you had in code surely. Building up the SQL statement - you can then use it directly, without the need to append it to a querydef.
It depends purely upon how you're trying to use it.

 

[JohnPapa]

So either we create a query which is stored or use it as an SQL subquery (inline) as I did. Correct?

Thanks,
John

 

[LPurvis]

Yeah, I don't really see any other implementation which is missing.
You can use a saved query (conceptually a view), create/alter one at runtime, or use ad hoc SQL wherever it's needed.