daveatthewell
New Member
- Joined
- Jul 28, 2006
- Messages
- 43
I have an Access "Membership-type" database with two tables as follows:
- [Countries] with the "CountryID" field set to autonumber and Primary Key and "ContactCountry" set to text
- [Members] with the usual data for a membership database including a "Country" field set to Number (using the Lookup wizard) and the following automatically set for the Lookup Tab Combo box, table/query, SELECT [Countries].[CountryID], [Countries].[ContactCountry] FROM [Countries] ORDER BY [ContactCountry]; . Another Field in this table is a Date Field "Renewal Date" which tells me the data when the member has renewed to.
I've created a Make Table query that uses the [Members] table (in which the Country field is a Combo Box display the NAME (not Record ID) of the Country) which returns all the fields of the [Members] table but only those records where the "Renewal Date" field contains a date greater than "a month ago" ie giving the member one months' grace and pastes them into a new table [Currently Active Members]
The SQL for the query is:
SELECT [Members].*, IIf([Renewal date]<=DateAdd("m",-1,Date()),False,True) AS Active INTO [Currently Active Members]
FROM [Members]
WHERE (((IIf([Renewal date]<=DateAdd("m",-1,Date()),False,True))=True));
The query runs OK and returns the correct data. However in the newly created table [Currently Active Members], the "Country" field now contains a number (which is of course the Record ID from the [Country] table and NOT the Text of the Country or as I would prefer it, the actual Combo Box with the name of the Country showing. Am I asking too much of Access to show the data as it was originally supplied to the query?
Any thoughts?
Dave Kennedy
Scotlandwell
- [Countries] with the "CountryID" field set to autonumber and Primary Key and "ContactCountry" set to text
- [Members] with the usual data for a membership database including a "Country" field set to Number (using the Lookup wizard) and the following automatically set for the Lookup Tab Combo box, table/query, SELECT [Countries].[CountryID], [Countries].[ContactCountry] FROM [Countries] ORDER BY [ContactCountry]; . Another Field in this table is a Date Field "Renewal Date" which tells me the data when the member has renewed to.
I've created a Make Table query that uses the [Members] table (in which the Country field is a Combo Box display the NAME (not Record ID) of the Country) which returns all the fields of the [Members] table but only those records where the "Renewal Date" field contains a date greater than "a month ago" ie giving the member one months' grace and pastes them into a new table [Currently Active Members]
The SQL for the query is:
SELECT [Members].*, IIf([Renewal date]<=DateAdd("m",-1,Date()),False,True) AS Active INTO [Currently Active Members]
FROM [Members]
WHERE (((IIf([Renewal date]<=DateAdd("m",-1,Date()),False,True))=True));
The query runs OK and returns the correct data. However in the newly created table [Currently Active Members], the "Country" field now contains a number (which is of course the Record ID from the [Country] table and NOT the Text of the Country or as I would prefer it, the actual Combo Box with the name of the Country showing. Am I asking too much of Access to show the data as it was originally supplied to the query?
Any thoughts?
Dave Kennedy
Scotlandwell