Mail Sorting Query

[mkuberski]

The only extra thing I may know about this data base is the size, some of these tables may have 500,000+ records. ortherwise, there shouldn't be any surprises.

As far as posting the solution, I appreciate any help you can offer at this point. I have been able to get a query to match people to locations, that is pretty simple. I think I am on the right track for finding the closest location for a particular person. The real trick was limiting each seminar to 10,000 people. As I said, I really appreciate all your help :^) THANK YOU!

[NeoPa]

I will post queries in here for you to set up in your database.
It could be done in the code, (I almost completed that), but I felt the code was better for the stuff that can only be done in code (DAO.RecordSet stuff). Queries are easier to understand if saved as QueryDefs (saved queries). This enables you to get a better grip of what's going on. Take name from first line and then copy and paste the following lines into the SQL pane of a new query.

[NeoPa]

First query :

Query=qrySeminar
SELECT LocationID,
       [ID] AS SemID,
       [Date]+CDate([Time]) AS SemDate
FROM [All_Seminars]
ORDER BY LocationID,[Date]+CDate([Time])

This will be used more than once. Once as a subquery, then within the VBA code.

[NeoPa]

Second query :

Query=qryFirstSeminar
SELECT LocationID,
       Val(Mid(Min(Format(qrySeminar.SemDate,'yyyymmddhhnnss') & 
                   qrySeminar.SemID),15)) AS SemID,
       Min(qrySeminar.SemDate) AS SemDate
FROM qrySeminar
GROUP BY LocationID

This is the subquery for seminars and returns only those seminars which are the earliest for each location.

[NeoPa]

Third query :

Query=qryNearest
SELECT NameID,
       Val(Mid(Min(Format([Distance],'0000000000') & 
                   Proximity_Results.LocationID),11)) AS LocationID
FROM Proximity_Results
GROUP BY NameID

This is the subquery for Account/Locations and returns only the closest location for each account.

[NeoPa]

Fourth query :

Query=qryAssignSeminar
INSERT INTO Account_Seminar
           (NameID, LocationID, SemID)
SELECT qryNearest.NameID,
       qryNearest.LocationID,
       qryFirstSeminar.SemID
FROM qryNearest INNER JOIN qryFirstSeminar
     ON qryNearest.LocationID=qryFirstSeminar.LocationID

This is the append query which inserts the basic data into Account_Seminar table.

[NeoPa]

This code should be copied and pasted into your project, preferably into an ordinary module (not class, form or report module).

'AssignSeminars assigns each account to a seminar at the nearest location.
'It ensures that no more than 10,000 are assigned to any one but the last.
Public Sub AssignSeminars()
    Dim strSQL As String
    Dim intRecNo As Integer
    Dim lngLocID As Long, lngSemID As Long
    Dim rstAS As DAO.Recordset, rstSem As DAO.Recordset

    'Delete current records from Account_Seminar table
    strSQL = "DELETE FROM [Account_Seminar]"
    Call DoCmd.RunSQL(strSQL)

    'Add each customer to the first seminar of nearest location
    Call DoCmd.OpenQuery("qryAssignSeminar")

    'Now the records all exist in the Account_Seminar table, it just
    'remains to move the overflow records (>10,000) to subsequent seminars
    strSQL = "SELECT LocationID,SemID " & _
             "FROM Account_Seminar " & _
             "ORDER BY LocationID"
    Set rstAS = CurrentDb.OpenRecordset(Name:=strSQL, _
                                        Type:=dbOpenDynaset, _
                                        LockEdit:=dbPessimistic)
    Set rstSem = CurrentDb.OpenRecordset(Name:="qrySeminar", _
                                         Type:=dbOpenSnapshot)
    lngLocID = 0
    With rstAS
        Call .MoveFirst
        Do While Not .EOF
            'On change of LocationID
            If ![LocationID] > lngLocID Then
                lngLocID = ![LocationID]
                intRecNo = 0
                'Moving on from previous seminar list to new one
                With rstSem
                    Do While Not .EOF And ![LocationID] < lngLocID
                        Call .MoveNext
                    Loop
                    If .EOF Then
                        'Should be impossible
                        lngSemID = 0
                    Else
                        lngSemID = ![SemID]
                    End If
                End With
            End If
            intRecNo = intRecNo + 1
            'On exhausting the 10,000 records allowed per seminar
            If intRecNo > 10000 Then
                With rstSem
                    If Not .EOF Then
                        Call .MoveNext
                        'If no further seminars available overload last one
                        If ![LocationID] = lngLocID Then lngSemID = ![SemID]
                    End If
                End With
            End If
            'Only update if default (first) is wrong one
            If ![SemID] <> lngSemID Then
                Call .Edit
                ![SemID] = lngSemID
                Call .Update
            End If
            Call .MoveNext
        Loop
    End With
    Set rstAS = Nothing
    Set rstSem = Nothing
End Sub

Please review previous posts as changes have been made to them since originally posted.
All should be up-to-date now though.

[mkuberski]

I'm assuming I should then run this module to run the queries. When I run this module, I get the following error:

Run-time error '3103':

Circular reference caused by alias 'LocationID' in query definition's SELECT list


It refers to the following line in the module:

Call DoCmd.OpenQuery("qryAssignSeminar")

I've tried tracing the reference but have not been able to. Thought another pair of eyes may help. If nothing else, if you can point me in the right direction, I'll continue to try and troubleshoot. Thanks again for all your help........

[NeoPa]

I'm assuming I should then run this module to run the queries. When I run this module, I get the following error:

Run-time error '3103':

Circular reference caused by alias 'LocationID' in query definition's SELECT list


It refers to the following line in the module:

Call DoCmd.OpenQuery("qryAssignSeminar")

I've tried tracing the reference but have not been able to. Thought another pair of eyes may help. If nothing else, if you can point me in the right direction, I'll continue to try and troubleshoot. Thanks again for all your help........

This is due to an unqualied field reference in the SQL somewhere.
I can look at it later (should be easy enough to find), but I'm at work now and tied up.
Give me two or three hours if you can't fix the problem yourself.

[NeoPa]

Mike,

I've checked through all the SQL I posted and I can't see any unqualified reference in there at all.
To help find this, can you run each of the four posted queries manually for me and tell me what, if any problems were found.
It should be safe to run the last one, as the code when run, will clear down any existing records from the table anyway.
I'm assuming you've copied and pasted the queries as posted and you redid them all after post #22 as requested.

[mkuberski]

Yes, I did recopy and paste all queries after that post.

When I run qrySeminar, I get the error
Data type mismatch in criteria expression
The query as saved is

SELECT LocationID, [ID] AS SemID, CDate([Date])+CDate([Time]) AS SemDate
FROM All_Seminars
ORDER BY LocationID, CDate([Date])+CDate([Time]);

When I run qryNearest, I get the error
Circular reference caused by alias 'LocationID' in query definition's SELECT list
the query as saved is

SELECT NameID, Val(Mid(Min(Format([Distance],'0000000000') & LocationID),11)) AS LocationID
FROM Proximity_Results
GROUP BY NameID;

When I run qryFirstSeminar , I get the error
Circular reference caused by alias 'SemID' in query definition's SELECT list
the query as saved is

SELECT LocationID, Val(Mid(Min(Format(SemDate,'yyyymmddhhnnss') & SemID),15)) AS SemID, Min(SemDate) AS SemDate
FROM qrySeminar
GROUP BY LocationID;

the append query returns the same error as qryNearest, probably because it calls it. The query as saved is

INSERT INTO Account_Seminar ( NameID, LocationID, SemID )
SELECT qryNearest.NameID, qryNearest.LocationID, qryFirstSeminar.SemID
FROM qryNearest INNER JOIN qryFirstSeminar ON qryNearest.LocationID=qryFirstSeminar.LocationID;

[NeoPa]

So, Nothing working at all then :(.
I'll have to look at this in more detail later this evening when I get home.
I may also try to organise some way to get database and data over to me to see. It's much easier that way, believe me.
Let me try going through your last post first though - I may find all the many problems :cry:

-Adrian.

[mkuberski]

That will be fine. Once again, thanks for all your help!!!. I'll also look at things a little closer this afternoon.

Mike

[NeoPa]

When I run qrySeminar, I get the error
Data type mismatch in criteria expression
The query as saved is

SELECT LocationID, [ID] AS SemID, CDate([Date])+CDate([Time]) AS SemDate
FROM All_Seminars
ORDER BY LocationID, CDate([Date])+CDate([Time]);

This should work fine.
Can you post some data that fails (create a data subset if necessary).
I can only assume that some data is missing or in a non-date format.

When I run qryNearest, I get the error
Circular reference caused by alias 'LocationID' in query definition's SELECT list
the query as saved is

SELECT NameID, Val(Mid(Min(Format([Distance],'0000000000') & Proximity_Results.LocationID),11)) AS LocationID
FROM Proximity_Results
GROUP BY NameID;

There's nothing wrong with this code (I just fixed it on the sly ;))!
LocationID (before ),11) must be qualified by Proximity_Resul ts.

When I run qryFirstSeminar , I get the error
Circular reference caused by alias 'SemID' in query definition's SELECT list
the query as saved is

SELECT LocationID, Val(Mid(Min(Format(qrySeminar.SemDate,'yyyymmddhhnnss') & qrySeminar.SemID),15)) AS SemID, Min(qrySeminar.SemDate) AS SemDate
FROM qrySeminar
GROUP BY LocationID;

Basically the same for this one.

Let's get these out of the way first the see what we have left.
Please copy again from earlier posts when I've doctored them.
The Code Window is mucking up the text so do a reply then copy the code from the Update Window instead.
BTW All queries have now been reposted :).

[mkuberski]

qryNearest is now working. YEAH!!!!

qryFirstSeminar and qrySeminar are showing a type mismatch. I wonder if for both it has to do with the Date and Time fields in the All_Seminars table. Here is the more of the metadata for the fields and a sample of the data.

Field Name Data Type Sample Data
Date Date/Time 11/21/2006
Time Text 11:00 AM

Let me know if this helps. Thanks......... .