If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Select two records then select the next two records
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? |
#2
|
|||
|
|||
Select two records then select the next two records
hi Ian,
On 24.02.2010 23:56, Ian wrote: 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 -- |
#3
|
|||
|
|||
Select two records then select the next two records
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 "Stefan Hoffmann" wrote: hi Ian, On 24.02.2010 23:56, Ian wrote: 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 -- . |
#4
|
|||
|
|||
Select two records then select the next two records
hi Ian,
On 01.03.2010 22:04, Ian wrote: 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 -- -- Access-FAQ http://www.donkarl.com/ KnowHow.mdb http://www.freeaccess.de Newbie-Info http://www.doerbandt.de/Access/Newbie.htm |
Thread Tools | |
Display Modes | |
|
|