Mail Sorting Query

[mkuberski]

I have a database with 3 tables, Mailing_List, All_Seminars and Proximity_Resul ts. 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_Resul ts 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_Resul ts. 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_Resul ts 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_Resul ts
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!Na meID (One) -> Proximity_Resul ts!NameID (Many)
All_Seminars!Lo cationID (Many) -> Proximity_Resul ts!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.