Select two records then select the next two records

[Ian]

I am still somewhat a beginner at this access stuff but I have something that
I am sure can be done but I don't know how to do it. I am using ACCESS 2003.
I have a database with contacts with auto id, last name, first name, ect.
At my company we everyday we get together and pray for two of the contacts.
So I would like to have a query that can select two contacts and then somehow
next time I run that query I get the next two contacts.
I am thinking I need to make a form that selects two contacts and stores
them in a table? and then next time I run a query it can just add a number
onto the previous Auto ID number from the previous two contacts. Any
solutions or ideas?

 

[Stefan Hoffmann]

hi Ian,

I am thinking I need to make a form that selects two contacts and stores
them in a table? and then next time I run a query it can just add a number
onto the previous Auto ID number from the previous two contacts.

You're right. You need a table to track the already presented contacts:

PresentationContact:

- idContact
Primary Key, Not Null, Referential Integrity to your Contact table with
Cascading Delete. Be carefully when creating the RI, drag the line from
Contact -> PresentedContact. Otherwise the Cascadinng Delete works in
the wronk direction.
- PresentationDate Date/Time Not Null

Selecting the todays contacts:

qryTodaysContacts:
SELECT [idContact]
FROM [PresentationContact]
WHERE DateValue([PresentationDate]) = DateValue(Now());

Use a INNER JOIN to get the data for the form, e.g.

SELECT C.*
FROM [Contact] C
INNER JOIN [qryTodaysContacts] T
ON C.[idContact] = T.[idContact];

Inserting the two contacts, may be done in the AutoExec macro:

INSERT INTO [PresentationContact] ([idContact], [PresentationDate ])
SELECT TOP 2 id, Now()
FROM [Contact] O
WHERE NOT EXISTS (
SELECT 1
FROM [qryTodaysContacts] I
WHERE I.[idContact] = O.[id]
)
ORDER BY id

Untested of course

mfG
--> stefan <--

 

[Ian]

Stefan,
Thank you very much for your help. I was able to perform some of the steps
you recomemnded but not all of them. If you could help me out with just a
bit more information and instructions it would be greatly appriciated. FYI
my main table with all the contacts is called "Addresses".
I did begin by creating another table and named it tblPresentedContact.
It has two fields one IDContact "Number" (Field Size - Long Integer, Decimal
Places - Auto, Required - Yes, Indexed - Yes no duplicates) and another
PresentationDate "Text" (Required - Yes, Indexed - No, IME Mode - No Control,
IME Sentence None) . I believe I set those field properties correctly. I
then went to the relationships and created a line from Contact[contactid] to
tblPresentedContact [IDContact] set it to RI and Cascading Delete. So
Relationship was One to One.

I then created a query
SELECT tblPresentedContact.IDContact
FROM Addresses INNER JOIN tblPresentedContact ON Addresses.AddressID =
tblPresentedContact.IDContact
WHERE (((tblPresentedContact.PresentationDate)=(Now())))
GROUP BY tblPresentedContact.IDContact;

Then you got to using an INNER JOIN and I got a little lost. I didn't
really know where to go for this. Also my MACRO skills aren't amazing so if
you would be so kind to add a some more info/directions that would be great.
Thanks again for being patient and helping me through this.

Ian

hi Ian,

I am thinking I need to make a form that selects two contacts and stores
them in a table? and then next time I run a query it can just add a number
onto the previous Auto ID number from the previous two contacts.

You're right. You need a table to track the already presented contacts:

PresentationContact:

- idContact
Primary Key, Not Null, Referential Integrity to your Contact table with
Cascading Delete. Be carefully when creating the RI, drag the line from
Contact -> PresentedContact. Otherwise the Cascadinng Delete works in
the wronk direction.
- PresentationDate Date/Time Not Null

Selecting the todays contacts:

qryTodaysContacts:
SELECT [idContact]
FROM [PresentationContact]
WHERE DateValue([PresentationDate]) = DateValue(Now());

Use a INNER JOIN to get the data for the form, e.g.

SELECT C.*
FROM [Contact] C
INNER JOIN [qryTodaysContacts] T
ON C.[idContact] = T.[idContact];

Inserting the two contacts, may be done in the AutoExec macro:

INSERT INTO [PresentationContact] ([idContact], [PresentationDate ])
SELECT TOP 2 id, Now()
FROM [Contact] O
WHERE NOT EXISTS (
SELECT 1
FROM [qryTodaysContacts] I
WHERE I.[idContact] = O.[id]
)
ORDER BY id

Untested of course

mfG
--> stefan <--
.

 

[Stefan Hoffmann]

hi Ian,

It has two fields one IDContact "Number" (Field Size - Long Integer, Decimal
Places - Auto, Required - Yes, Indexed - Yes no duplicates) and another
PresentationDate "Text"

The PresentationDate should have the data type Date/Time.

Then create the helper query:

qryTodaysContacts:
SELECT [idContact]
FROM [tblPresentedContact]
WHERE DateValue([PresentationDate]) = DateValue(Now());

Insert some test date and run the query to verify that it works.

I then created a query
SELECT tblPresentedContact.IDContact
FROM Addresses INNER JOIN tblPresentedContact ON Addresses.AddressID =
tblPresentedContact.IDContact
WHERE (((tblPresentedContact.PresentationDate)=(Now())))
GROUP BY tblPresentedContact.IDContact;

Its correct to use a query here. Use the helper query instead of the
table directly:

SELECT A.*
FROM [Addresses] A
INNER JOIN [qryTodaysContacts] T
ON A.[AddressID] = T.[idContact];

and test it. btw, you should use your naming convention. So I would
rename some things:

tblPresentedContact -> tblPresentedAddress
IDContact -> AddressID
qryTodaysContacts -> qryTodaysAddresses

Then you got to using an INNER JOIN and I got a little lost. I didn't
really know where to go for this.

This means that you can use this kind of SQL statement in a forms record
source directly instead of creating a separate query.

Also my MACRO skills aren't amazing so if
you would be so kind to add a some more info/directions that would be great.

Create an additional query using the INSERT SQL I've posted. Adapt your
table and field names. And test it.

Create a macro named 'AutoExec'. This macro will automatically executed
when the database is opened. So you can ensure that todays contacts are
prepared.

In the macro choose the run query action and reference the INSERT query
you've created previously.


mfG
--> stefan <--