|
| 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
January 12th, 2009, 09:07 AM
posted to microsoft.public.access.queries
|
|||
|
|||
MS Access database query
In my database I have non-automatic reference numbers (entered manually).
How do I say in a query that I would like all the numbers NOT currently used? Thanks in advance. 
|
|
#2
January 12th, 2009, 01:27 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
MS Access database query
Henrietta
There are (at least) a couple ways to approach this. One uses the query design window, one uses SQL. Using the query design window, create a query that finds all the numbers used. Then build a second query of the original table using the "unmatched" query (wizard) against the first query. The SQL approach uses an embedded sub-select. I have more experience with the former, but maybe another poster could offer more specifics on the sub-select approach... Good luck! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Henrietta" wrote in message ... In my database I have non-automatic reference numbers (entered manually). How do I say in a query that I would like all the numbers NOT currently used? Thanks in advance.
|
|
#3
January 13th, 2009, 01:33 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
MS Access database query
Brilliant !
I used your unmatched theory & it works. I made a "dummy" table with just references listed on Excel & pasted appended into the new table. 1 query based on the original data & a 2nd based on the dummy data, with the unmatched query to pull them together. Works perfectly. Thank you Jeff ! "Jeff Boyce" wrote: Henrietta There are (at least) a couple ways to approach this. One uses the query design window, one uses SQL. Using the query design window, create a query that finds all the numbers used. Then build a second query of the original table using the "unmatched" query (wizard) against the first query. The SQL approach uses an embedded sub-select. I have more experience with the former, but maybe another poster could offer more specifics on the sub-select approach... Good luck! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Henrietta" wrote in message ... In my database I have non-automatic reference numbers (entered manually). How do I say in a query that I would like all the numbers NOT currently used? Thanks in advance.
|
|
#4
January 19th, 2009, 03:06 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
MS Access database query
Henrietta
If you created an extra dummy table, you worked too hard! You can use a query to find all the records in one set of data (e.g., Excel), then refer to that query in a second query. Glad you found a workable solution, though! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Henrietta" wrote in message ... Brilliant ! I used your unmatched theory & it works. I made a "dummy" table with just references listed on Excel & pasted appended into the new table. 1 query based on the original data & a 2nd based on the dummy data, with the unmatched query to pull them together. Works perfectly. Thank you Jeff ! "Jeff Boyce" wrote: Henrietta There are (at least) a couple ways to approach this. One uses the query design window, one uses SQL. Using the query design window, create a query that finds all the numbers used. Then build a second query of the original table using the "unmatched" query (wizard) against the first query. The SQL approach uses an embedded sub-select. I have more experience with the former, but maybe another poster could offer more specifics on the sub-select approach... Good luck! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Henrietta" wrote in message ... In my database I have non-automatic reference numbers (entered manually). How do I say in a query that I would like all the numbers NOT currently used? Thanks in advance.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
