Mail Sorting Query

[mkuberski]

I have a database with 3 tables, Mailing_List, All_Seminars and Proximity_Results. The Mailing_List is a list of customers and their address, All_Seminars is a list of seminar locations and the dates and times of the locations (each seperate date/time/location combination is a seperate entry) and Proximity_Results lists how close each customer is to various locations (only to location, it does not distinguish between different dates or times). I need to develop a query that first assigns people to the first seminar at the location that is closest to them. Then, I need to keep track of how many people are assigned to each seminar and when the number reaches 10,000, check if there is another seminar date/time at that location. If so, I start to assign them to the next available date/time. If not, I keep assigning them to the current seminar. Any help would be appreciated and if you need any other information, please let me know........

Mike

[NeoPa]

What do you use to determine proximity?
This looks as if it will require individual record processing using DAO.RecordSet or ADODB.RecordSet. I can't think of a way to process this through basic SQL.

[mkuberski]

There is a seperate program doing this which produces the information for the table Proximity_Results. This table has 4 fields, A ID field I have let Access add as a primary index, a NameID field which links back to the Mailing_List table (each value is in the Proximity_Results table multiple times and in the Mailing_List table only once) a LocationID field that links back to the All_Seminars table (each value is listed multiple times in both tables) and a Proximity field which is the milage between the NameID and LocationID in the record (number is formated XX.X)

[NeoPa]

Duh, My bad. The info I was thinking I needed was already posted (I don't need to know HOW you work out proximity, just where that info is available :().
Give me a while and I'll have a more detailed look at this (sounds like fun).
If I can squeeze it into SQL code then I will, otherwise I'm not very good at DAO & ADODB processing, so I'll just do what I can and explain the rest.

[NeoPa]

Further Info:
It would be helpful if you could list your tables' metadata (Table name on top line followed by field names, types and PK/FK where indexed, one field per line).
This would just make it a whole lot easier to see what I'm working with.

[mkuberski]

Database Info

Table Mailing_List
ID - PK
NameID - Indexed
Item
First Name
Last Name
Address 1
Address 2
City
State
Zip
ZipPlus4
County Code
County Name
Title code
Barcode
Keycode
Barcode Compressed
Exact Age

Table Proximity_Results
ProximityID - PK
LocationID - Indexed
NameID - Indexed
Distance - Indexed

Table All_Seminars
ID - PK
Template
LocationID - Indexed
County
GA Code
Phone Number
Seminar Location
Date
Time
Address 1
City
State
AltTemplate
Zip
Field14
Field15
Max Capacity

Mailing_List!NameID (One) -> Proximity_Results!NameID (Many)
All_Seminars!LocationID (Many) -> Proximity_Results!LocationID (Many)

[NeoPa]

Perfect.
I will have another look at this and hope to have a response by tomorrow.
I need to go out now for a while.
I've looked at the problem already though, it's not easy and may require changes to the structure (considering the addition of a Location table but will have to give it some more thought).
Certainly it will require grouping all the items into the first seminar in the first pass then somehow moving all but the first 10,000 in a separate process.

[mkuberski]

As a followup, the three tables I currently have are the data files I receive so additional tables would need to be created from these files. After I finish processing the data in these files, they will in essence be tossed and three new files with new data will be given to me and a new round of processing will begin.

Mike

[NeoPa]

Noted Mike.

[NeoPa]

This is proving quite involved.
I'm assuming that all IDs are numeric except the NameID.
I would definitely recommend normalising the database but I suppose as the data is supplied from an outside source your hands are tied to a certain extent.
I will work to the current situation then, but the recommendation still stands if you can find a way to do it.

I will be looking to create a new table.

Tablename=Account_Seminar
AS_ID; AutoNumber; PK
NameID; Text; FK
LocationID; Number; FK
SemID; Number; FK
SemIndex; Number
SemMaxCapacity; Number

This should end up as the data assigning all your accounts to their correct seminars. This will need to be set up in your database as my code will append data to it.
Further update tomorrow.

[mkuberski]

Actually, all IDs, including the NameID field are numeric. Upon importing the text file, they are coming in as text field but that is easily changed.

[mkuberski]

Sorry, I didn't fully read your last post before answering......

The Max Capacity field in the All_Seminars field is not used necessarily in my piece. Itis more used by the client after they receive responses to these invitations. Obviously, the percentage of responses to invitations sent is pretty low so the limit for invitations sent for each seminar is about 10,000 (except for the last seminar at any given location)

[NeoPa]

We should end up with something of this form then?
BTW well spotted and deduced - that was my intention.

Tablename=Account_Seminar
AS_ID; AutoNumber; PK
NameID; Number; FK
LocationID; Number; FK
SemID; Number; FK

I need to get on and look at this then.
You're happy with the idea of designing this table and leaving it available?

[mkuberski]

From what I am seeing, that looks good. I am fine with creating and leaving this table available. As I need to move on to new data, I can simply scrub this table clean.

[NeoPa]

From what I am seeing, that looks good. I am fine with creating and leaving this table available. As I need to move on to new data, I can simply scrub this table clean.

The scrubbing is a very simple part of the process so will be automated in my procedure.

Just to mention :
I generally don't go in for providing full answers to complicated questions like this one. As I had promised already, before I realised the full scope though, I will keep to that and post a procedure to do the whole job. This may need some amendment on your part as :
I don't have access to real data so can't test it.
You may have some info about the database which I'm not aware of.

[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_Results.

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..........

[NeoPa]

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..........

So, the query fails when you apply it to a table with just this one record in it? That seems unlikely.
From my earlier post, it's important to find a (relatively small) dataset which fails. I'm guessing there's a record or two somewhere that doesn't follow format rules for the date or the time. If you can do that, and the database is self-contained, then you can PM me with your e-mail address (trawler proof preferably - '.'s or '@'s replaced by the words should do it) and I will e-mail you with mine. At this point you can e-mail me with a compressed version of your cut-down database. This will involve some work on your part too as I will expect non-relevant stuff to be removed before doing a Compact and Repair then compressing the results (Zip is fine as are various other types).

[NeoPa]

HANG FIRE!
Are you saying your date is a Date/Time but your Time is Text?
In fact, please go back to post #5 and repost your MetaData (in a [code] box) with all the info if you would. That would be simplest.
This could well help us to resolve this problem without all the other, more complicated, stuff.

[mkuberski]

Further Info:
It would be helpful if you could list your tables' metadata (Table name on top line followed by field names, types and PK/FK where indexed, one field per line).
This would just make it a whole lot easier to see what I'm working with.

Table Mailing List
 
ID - AutoNumber - PK
NameID - Text - FK
Item - Text - None
First Name - Text - None
Last Name - Text - None
Address 1 - Text - None
Address 2 - Text - None
City - Text - None
State - Text - None
Zip - Text - None
ZipPlus4 - Text - None
County Code - Text - FK
County Name - Text - None
Title code - Text - FK
Barcode - Text - FK
Keycode - Text - FK
Barcode Compressed - Text - None
Exact Age - Text - None
 
Table Proximity_Results
ProximityID - AutoNumber - PK
LocationID - Text - FK
NameID - Text - FK
Distance - Number - FK
 
Table All_Seminars
ID - AutoNumber - PK
Template - Text - None
LocationID - Number - FK
County - Text - None
GA Code - Text - FK
Phone Number - Text - None
Seminar Location - Text - None
Date - Date/Time - None
Time - Text - None
Address 1 - Text - None
City - Text - None
State - Text - None
Template - Text - None
Zip - Text - None
Field14 - Text - None
Field15 - Text - None
Max Capacity - Number - None
 

This is based on how Access imported this data. I can try reimporting the data and setting the seminar date and time fileds to date/time type manually.

[NeoPa]

This is based on how Access imported this data. I can try reimporting the data and setting the seminar date and time fileds to date/time type manually.

That shouldn't be necessary Mike. I can work with these.
This should actually be easier to work with (I was treating both as text fields) - it was just a surprise. I suppose it makes sense really but it blind-sided me.
As before, will have to work on this after work.

[NeoPa]

Try grabbing the first query (qrySeminar) again.
I've 'fixed' it so it should work now.
Let me know after you test it please.