Access 2007 query to combine records from two different tables

[Vilius01]

Hi all



I am a novice access user and have been working on this access 2007 patient database that should ease the track of health records and generate invitation letters to perform specific tests. I have a selection query that filters patients I need to invite and a table with stored available visit times. I need to to create a query that links patients to defined number of free visit times e.g. for the upcoming month or so, one patient per one time record. Any ideas how to do that? Thank in advance.

[ItsMe]

What is the rule for free visits?

[June7]

As ItsMe asks, what rule(s) should Access follow in associating patient with free visit time slot? What decision process would you use if you manually selected time slot for each patient (this is an option)? To automate a batch assignment, I suspect these rules cannot be built into query and this will require VBA code.

[Vilius01]

Thanks for your replies, well patients will have an opportunity to change their visit time later on, when they'll receive invitation letter, so probably the time can be assigned in the order as patients are sorted in the selection query i.e. patient in the top row is given the earliest time and so on. Despite that there are no other rules.

[June7]

Just reinforces my assessment that this will require VBA code. The code would open recordset objects of Patients and VisitTimes tables with appropriate filtering, then loop through the recordsets and insert records in Appointments table.

[Vilius01]

Would anyone help me with the code since VBA is smth I am completely unfamiliar.

[June7]

This sounds like significant effort and know nothing about your db structure.

Suggest you need to become familiar with coding. Start with:

http://office.microsoft.com/en-us/ac...010341717.aspx

http://chandoo.org/wp/2011/05/13/int...o-programming/

http://allenbrowne.com/ser-29.html

[ItsMe]

I would add that you need to understand the RDBMS side of things before you can start coding. Perhaps you can start by learning RDBMS fundamentals so you can create the correct table structure.

[Vilius01]

Sorted without vba, two additional empty tables were needed with all necessary fields i.e time in the first and patients in the second, plus auto-number ID field in both tables, than you make two append queries one for patients, the other for time. When you append records to empty tables autonumber assigns each entry with number which is your linked field in selection query to select patients and time in one record set. Afterwards you can make delete query to erase data in additional tables. You put everything in macro and works just fine, except the fact that autonumber does not reset its count, for that you need to restart database and compact it on close.