邮件排序查询 [英] Mail Sorting Query

问题描述

我有一个包含3个表的数据库，Mailing_List，All_Seminars和Proximity_Results。 Mailing_List是客户及其地址的列表，All_Seminars是研讨会位置列表以及位置的日期和时间（每个单独的日期/时间/位置组合是单独的条目），Proximity_Results列出了每个客户与各种客户的接近程度。位置（仅限于位置，不区分不同的日期或时间）。我需要开发一个查询，首先将人员分配到最接近他们的位置的第一个研讨会。然后，我需要跟踪每个研讨会分配了多少人，当数量达到10,000时，检查该位置是否有另一个研讨会日期/时间。如果是这样，我开始将它们分配到下一个可用的日期/时间。如果没有，我会继续将它们分配给当前的研讨会。任何帮助将不胜感激，如果您需要任何其他信息，请告诉我........


Mike

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

推荐答案

你用什么来确定接近度？

这看起来好像需要使用DAO.RecordSet或ADODB.RecordSet进行单独的记录处理。我无法想到通过基本SQL处理它的方法。

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.

有一个单独的程序执行此操作，它为表Proximity_Results生成信息。此表有4个字段，一个ID字段我让Access添加为主索引，一个NameID字段链接回Mailing_List表（每个值在Proximity_Results表中多次，在Mailing_List表中只有一次）一个LocationID字段链接回All_Seminars表（每个值在两个表中多次列出）和一个Proximity字段，它是记录中NameID和LocationID之间的关系（数字格式为XX.X）

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)

Duh，我的坏。我认为我需要的信息已经发布（我不需要知道你如何计算接近度，只是提供信息的地方:(）。

给我一点时间，我我会更详细地看一下这个（听起来很有趣）。

如果我可以将它压缩成SQL代码那么我会，否则我不太擅长DAO& ADODB处理，所以我会尽我所能并解释其余部分。

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.

这篇关于邮件排序查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！