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
34 2714 NeoPa 32,568
Recognized Expert Moderator MVP
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.
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 32,568
Recognized Expert Moderator MVP
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 32,568
Recognized Expert Moderator MVP
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.
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 32,568
Recognized Expert Moderator MVP
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.
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 32,568
Recognized Expert Moderator MVP NeoPa 32,568
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Karen Bailey |
last post by:
Hi,
I am attempting to write a complex query to group sets of data. I have my
query working correctly, but i ran into a problem with sorting.
I want to sort my query by a string field.
Is there a way around sorting using a field other than numeric of a query
containing a union?
|
by: Mike MacSween |
last post by:
tblCourses one to many to tblEvents.
A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.
At the moment the printed output is usually going to Word. It's turning into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.
|
by: luca varani |
last post by:
I would like to sort the results of a crosstab query by the aggregate
function it automatically generates (total of the values in each column of
the crosstab).
If I simply put "ascending" in the sort field of the design view I get the
following error message:
"cannot have aggregate function in order by clause".
I had no success trying to edit the sql statement directly, either.
I looked quite a while for help inside access or online...
|
by: nn0410 |
last post by:
I have a report whose record source is a query. The query includes an
ORDER BY clause that sorts on a particular set of columns. I would
like to be able to run the same report with the same input data, sorted
on a different set of columns. Aside from copying (^C, ^V) the report
and changing the Record Source property in the new copy to another
query, is there an elegant way to accomplish this?
Thanks
Rick
|
by: Dylan Parry |
last post by:
Hi folks,
I have a database that contains records with IDs like "H1, H2, H3, ...,
Hn" and these refer to local government policy numbers. For example, H1
might be "Housing Policy 1" and so on. For some more insight, not all
policies will be prefixed with an "H", an in fact they could be prefixed
with *any* letter combination, but they will always end with a number.
When I retrieve them from the database I use "ORDER BY id" to get them...
| |
by: Jimmy |
last post by:
Is there a way to sort/group a report based on the second column of a combo
box, i.e. the text associated with the primary key number?
|
by: jjjoic |
last post by:
Hi,
I use Access 2003 to generate the back-end data for a ColdFusion report at work. The report is sorted by a column and based on the sorting, rankings are assigned to each row(i.e. the biggest value gets No. 1, and then the row of the second biggest value is ranked as No. 2). Each task owners will then have to attack their owning issues based on the ranking.
The No. 1, 2, 3,... rankings are generated in this way:
1. An...
|
by: Bob Laubla |
last post by:
Hello
I have a very complex maketable query with many records and involving multiple VB functions which call other functions.
I need this table to be sorted by the first field.
But no matter what I do, there is a small chance of the table coming out unsorted.
I have tried sorting it with the 'make table' query.
I have tried sorting it with Word during output (my current configuration runs 4 of these).
I have tried reading it into an...
|
by: =?Utf-8?B?RGFu?= |
last post by:
I am reposting a question from about 3 weeks ago ("sorting capability").
I have an aspx page in which I get the data from a database dynamically,
through C# code, by creating a dynamic table using TableHeaderCell and
TableHeaderRow.
The data is binded to the table by using a DataSet and Data Reader.
The responses on the forum made me think that it would be easier to sort the
data in the table (when clicking on the header column), by...
|
by: nagmvs |
last post by:
Hi to all
I have one table with 6 columns and 20 rows.I want to sort each and every column when i click the column name in the table.
for sorting i create one more page.when i click the column name it connect to sorting page and then page will be sorting. like this i wnat to write the code.
i wrote some code,but it not working properly.my code is
For first page:
<%userdetails=session("userdetails")
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |