Pulling out right ID number

In a criteria form I have 10 comboboxes of which, if filled, its values
are used as criteria in corresponding queries (on different linked tables) 
which all result in an independent set of the same type of ID numbers.

Now I only want to pull out those ID numbers of the records that meet all 
criteria. With every solution I think of I end up with a lot of match 
queries and I assume there is an easier way. What would be the best way to 
do this?

Thanks,

Lars 


0
Lars
11/15/2009 8:21:29 PM
access 16762 articles. 3 followers. Follow

6 Replies
664 Views

Similar Articles

[PageSpeed] 10

Hi Lars,
have a look at this search form
http://www.allenbrowne.com/ser-62.html

It has a very easy and efficient way to get the criteria.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"Lars Brownies" <Lars@Brownies.com> wrote in message 
news:hdpns7$122b$1@textnews.wanadoo.nl...
> In a criteria form I have 10 comboboxes of which, if filled, its values
> are used as criteria in corresponding queries (on different linked tables) 
> which all result in an independent set of the same type of ID numbers.
>
> Now I only want to pull out those ID numbers of the records that meet all 
> criteria. With every solution I think of I end up with a lot of match 
> queries and I assume there is an easier way. What would be the best way to 
> do this?
>
> Thanks,
>
> Lars
> 


0
Jeanette
11/15/2009 10:01:24 PM
Thanks. The only problem in my case is that the criteria are set on the main 
table as well as child tables. It seems to me that the filter option, as 
shown in Allen's example form isn't an option then.

Lars

"Jeanette Cunningham" <nnn@discussions.microsoft.com> schreef in bericht 
news:%23cDbu8jZKHA.1640@TK2MSFTNGP06.phx.gbl...
> Hi Lars,
> have a look at this search form
> http://www.allenbrowne.com/ser-62.html
>
> It has a very easy and efficient way to get the criteria.
>
>
> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>
>
> "Lars Brownies" <Lars@Brownies.com> wrote in message 
> news:hdpns7$122b$1@textnews.wanadoo.nl...
>> In a criteria form I have 10 comboboxes of which, if filled, its values
>> are used as criteria in corresponding queries (on different linked 
>> tables) which all result in an independent set of the same type of ID 
>> numbers.
>>
>> Now I only want to pull out those ID numbers of the records that meet all 
>> criteria. With every solution I think of I end up with a lot of match 
>> queries and I assume there is an easier way. What would be the best way 
>> to do this?
>>
>> Thanks,
>>
>> Lars
>>
>
> 


0
Lars
11/16/2009 6:00:07 AM
Don't know it will help. May be you should include the SQL search string with
"DISTINCT"?

Lars Brownies wrote:
>Thanks. The only problem in my case is that the criteria are set on the main 
>table as well as child tables. It seems to me that the filter option, as 
>shown in Allen's example form isn't an option then.
>
>Lars

-- 
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

0
AccessVandal
11/16/2009 9:08:13 AM
Mmm,
in that situation, I combine all the tables into a query for the main form 
and get rid of the subform.

If that is not an option for you, I don't know what else to suggest.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Lars Brownies" <Lars@Brownies.com> wrote in message 
news:hdqpp7$1e5m$1@textnews.wanadoo.nl...
> Thanks. The only problem in my case is that the criteria are set on the 
> main table as well as child tables. It seems to me that the filter option, 
> as shown in Allen's example form isn't an option then.
>
> Lars
>
> "Jeanette Cunningham" <nnn@discussions.microsoft.com> schreef in bericht 
> news:%23cDbu8jZKHA.1640@TK2MSFTNGP06.phx.gbl...
>> Hi Lars,
>> have a look at this search form
>> http://www.allenbrowne.com/ser-62.html
>>
>> It has a very easy and efficient way to get the criteria.
>>
>>
>> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>>
>>
>>
>> "Lars Brownies" <Lars@Brownies.com> wrote in message 
>> news:hdpns7$122b$1@textnews.wanadoo.nl...
>>> In a criteria form I have 10 comboboxes of which, if filled, its values
>>> are used as criteria in corresponding queries (on different linked 
>>> tables) which all result in an independent set of the same type of ID 
>>> numbers.
>>>
>>> Now I only want to pull out those ID numbers of the records that meet 
>>> all criteria. With every solution I think of I end up with a lot of 
>>> match queries and I assume there is an easier way. What would be the 
>>> best way to do this?
>>>
>>> Thanks,
>>>
>>> Lars
>>>
>>
>>
>
> 


0
Jeanette
11/16/2009 8:03:56 PM
Distinct will only make sure that all unique ID numbers are there. It won't 
take care of my need to get the ID numbers that meet all criteria.

Lars

"AccessVandal via AccessMonster.com" <u18947@uwe> schreef in bericht 
news:9f316863e072d@uwe...
> Don't know it will help. May be you should include the SQL search string 
> with
> "DISTINCT"?
>
> Lars Brownies wrote:
>>Thanks. The only problem in my case is that the criteria are set on the 
>>main
>>table as well as child tables. It seems to me that the filter option, as
>>shown in Allen's example form isn't an option then.
>>
>>Lars
>
> -- 
> Please Rate the posting if helps you.
>
> Message posted via http://www.accessmonster.com
> 


0
Lars
11/17/2009 7:17:02 AM
Thanks. I'll do some more fiddling.

"Jeanette Cunningham" <nnn@discussions.microsoft.com> schreef in bericht 
news:%23rzTxfvZKHA.3992@TK2MSFTNGP04.phx.gbl...
> Mmm,
> in that situation, I combine all the tables into a query for the main form 
> and get rid of the subform.
>
> If that is not an option for you, I don't know what else to suggest.
>
>
> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>
> "Lars Brownies" <Lars@Brownies.com> wrote in message 
> news:hdqpp7$1e5m$1@textnews.wanadoo.nl...
>> Thanks. The only problem in my case is that the criteria are set on the 
>> main table as well as child tables. It seems to me that the filter 
>> option, as shown in Allen's example form isn't an option then.
>>
>> Lars
>>
>> "Jeanette Cunningham" <nnn@discussions.microsoft.com> schreef in bericht 
>> news:%23cDbu8jZKHA.1640@TK2MSFTNGP06.phx.gbl...
>>> Hi Lars,
>>> have a look at this search form
>>> http://www.allenbrowne.com/ser-62.html
>>>
>>> It has a very easy and efficient way to get the criteria.
>>>
>>>
>>> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>>>
>>>
>>>
>>> "Lars Brownies" <Lars@Brownies.com> wrote in message 
>>> news:hdpns7$122b$1@textnews.wanadoo.nl...
>>>> In a criteria form I have 10 comboboxes of which, if filled, its values
>>>> are used as criteria in corresponding queries (on different linked 
>>>> tables) which all result in an independent set of the same type of ID 
>>>> numbers.
>>>>
>>>> Now I only want to pull out those ID numbers of the records that meet 
>>>> all criteria. With every solution I think of I end up with a lot of 
>>>> match queries and I assume there is an easier way. What would be the 
>>>> best way to do this?
>>>>
>>>> Thanks,
>>>>
>>>> Lars
>>>>
>>>
>>>
>>
>>
>
> 


0
Lars
11/17/2009 7:18:13 AM
Reply:

Similar Artilces:

Display Numbers as Words
I have found plenty of answers for this including an old post from 2004 at http://tinyurl.com/4m4sp, and Microsoft's website (http:// support.microsoft.com/default.aspx?scid=KB;EN-US;Q213360) however, they all translate the number into currency. How would I modify this code to treat the number just as numbers, not currency? i.e. 1,261 becomes "one thousand two hundred sixty one"? In my current scenario, there will not be any decimals. Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(...

generating part numbers
I wrote a simple spreadsheet that we enter all of our product we purchase in, and it calculates the reselling price very nicely. Over time this has grown to have a few thousand part numbers. Now we are going to produce our own catalog, and I would like to change the supplier part number, into a unique number to publish in our catalog. Doing this individually would take forever, so I was hoping to make a simple formula to automate it. By multiplying the supplier part number by 1.05 for example would work, but the problem is that many of the manufacturers part numbers have a letter in it. F...

Excel 2003 & numbers display problem
In Excel 2003 Tools>Options>Edit>Fixed Decimal Places is set at 2 but values entered in a cell are displayed differently from earlier versions: Cell Entry Displayed in 2003 Displayed in Excel XP 175 1.75 1.75 175. 1.75 175.00 175.0 175.00 175.00 It now seems necessary to enter a zero after the decimal point for the number to show up correctly as a whole number and its leading to many mistakes in data entry and becoming extremely...

text displayed represents number...
Working on weekly Rota. I have a formula for calculating hours thanks to http://www.cpearson.com/excel/DateTimeWS.htm but need to add to this for Bank Holidays, absences etc. So, if "Ann's" week included a bank holiday on Monday, the Rota for this day will have a "B" in the first of Monday's 2 columns (representing start & end times) and "H" in the second. This paid day equates to 7 hours so how do I make "B" and "H" each represent 3.5 hours? (or is there a much much easier solution to the whole thing? Please say yes...) Any help gr...

Postive negitive numbers in conditional formatting of a report
I have a money field, I like money owed in red, zero blanace white and credited amounts above bill in green. Seems no matter what I due it treats Pos & Neg numbers and positive numbers. using access 2000. -- Frank Frank wrote: > I have a money field, I like money owed in red, zero blanace white and > credited amounts above bill in green. Seems no matter what I due it > treats Pos & Neg numbers and positive numbers. using access 2000. Check the help file for the format property for numbers. When used against numbers the format property has four sections separated by semi...

Adding Sequential numbers to a new feld in access table.
I am working with a table which is actually a merge of 14 other tables, therefore there is no real index key. Table 1 Index was 1-5000 Table 2 index was 1-5000 In the merged database there are about 52,000 records, but no index key. I added a field to the table named Index and would like to populate that field with a unique index number. Does anyone have any ideas on how I could create an update query which would add 1-52,000 to this field. Thank you for your help. rjcmi wrote: >I am working with a table which is actually a merge of 14 other >tables, therefore t...

Numbering of duplicates
Hi. I need to do numbering of duplicate values and assign these numbers to a table. Example: Table: Field1 1 2 3 2 3 2 4 1 Reult I need: Field1::Field2 1::1 2::1 3::1 2::2 3::2 2::3 4::1 1::2 where "::" is a field separator. Additional assumptions: - Field1 is a text field - Updating is necessary (new data will appear in Field1 and should also be numbered) Thanks for help Kamil Something like SELECT (SELECT COUNT(*)+1 FROM P1 WHERE P1.ID < P.ID AND P1.FIELD1=P.FIELD1) AS ROWNUM, P.* FROM P HtH Pieter <kamil.jedrzejewski@gmail.com> wrote in message news:1193131909....

EXTRACTING NUMBERS FROM A TEXT CELL
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6EC75.288A0320 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! Currently I am unable to download information from the accounting system = in a better format. So one line of information come into Excel in one = cell, hence, the example below:=20 =1B@1 0325=1B@1 OPENING BALANCE : .....ESTIMATED COST - = MATERIAL=1B@1< 813,936.29 There are two things I need to learn here: 1) How can I extract just the number.=20 1a) I tried t...

Can I enter a number as a category?
I am trying to keep a record of how many tickets I give away for an event and would like to record this against contacts in Outlook. What is the best way to do this? Well adding them to a specific category like "Ticket" is indeed a good idea. You can then sort by category and easily se who you gave a ticket to. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "stoptalk" <stoptalk@discussions.microsoft.com> wrote in message news:5FC8752...

Query same category id
May i know how to query all the jobnumber where it is in the same category ID? Because I want to use this as my list in combo box -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1 I'm not clear yet what you are trying to achieve. One interpretation of your question would be that you wish to group by category ID. Another could be that you want to find jobnumbers that fall into a (pre-selected) category ID. Have you tried creating a new query that includes your conditions (selection), then using that query as the source for ...

Free/Busy Rights
Does anyone know if you can display the rights granted to users (via Outlook client) in the Exchange System Manager? For example: If one user grants another user Author rights to his Calendar, is there a place in ESM that I can see this? I looked at the Mailbox rights in ADUC, but that doesn't display it. Thanks! On Mon, 28 Aug 2006 09:18:02 -0700, Scott <Scott@discussions.microsoft.com> wrote: >Does anyone know if you can display the rights granted to users (via Outlook >client) in the Exchange System Manager? For example: If one user grants >another user Author rig...

Columns now numbers rather than letters how do i get letters back
Somehow I have managed to accidentally change the column references to numbers rather than letters. How do I get these back? Not sure of the correct terminology but I am referring to where the rows are usually numbers and the columns usually letters. My columns are now also numbers! In EXCEL 2007:- Office Button (top left hand corner) Excel Options (lower right hand corner) Formulas - make sure that there is no tick in the box called:- R1C1 reference style EXCEL 2003 Tools / Options / General tab – remove tick from R1C1 Reference style. If my comments have he...

Document Numbers already exists
Has anyone come across this error message in converting a fulfillment order? 'Document number already exists'? ...

Numbers will not copy between worksheets
I have an Excel workbook with two worksheets. One named Assests and the other named Print. Users enter data on the assets sheet and then run a macro that prints out the 'Print' sheet in a certain way. I have cells on the Print sheet linked to cells on the Assets sheet with a formula like this one: =IF(Assets!B10>"",(""&Assets!B10&""),"") This works great unless the value entered is just a number. If it's just a number with no letters, the value from the Assets sheet does not show in the corresponding cell on the Print sheet....

How to determine the number of decimal places in a number?
Hi, Is there an idiomatic way of determining the number of decimal places in a number (i.e. 0.123 = 3, 0.123456 = 6 etc.)? Other than converting the number to a string and using string functions to pull the characters after the decimal place? Thanks, Frank. Frank declare @v sql_variant set @v=0.123111 select SQL_VARIANT_PROPERTY(@v, 'Precision') as BaseType "Frank" <francis.moore@gmail.com> wrote in message news:0b65f019-7eb7-412d-9318-a7c82e388ea3@m16g2000yqc.googlegroups.com... > Hi, > > Is there an idiomatic way of determin...

How do I change Excel default number?
Recently upgrade to Excel 2007. When I type in 125000 and press enter key it changes to 12.5 and when I type in 5000 it gives me 0.5 My question is how do I change to setup so that what ever I typed in it stay that way? Thank you for your help in advance. Office button (large round, top left)>Excel Options>Advanced tab>Uncheck "Automatically insert a decimal point" -- Kind regards, Niek Otten Microsoft MVP - Excel "PatrickR" <PatrickR@discussions.microsoft.com> wrote in message news:E8F8583A-DF98-4CC9-AD44-EC5CBD2E14F0@microsoft...

16 digit number wont keep alteration unless format cell to text
Using excel 2000 recieving data from client with 16 numeral characters within cells cells show an abreviated listing for the number (2.06E+15) When editing the the number (wanting to change the last number by 1 digit) i.e. 2059510000000000 - change to - 2059510000000001 this change will not be kept?? I format the cell to "numbers" with no decimal point as a whole number is required Any ideas?? Is this number to long for excell 2000?? I'm not a big excel user Cheers Croc001 Excel, like most other spreadsheet programs, uses IEEE format for storing floating point numbers. Tha...

Numbers change values by themselves!
I have formatted a cell as Number, no decimals. If I put in the number 11111111111111111111 I get 11111111111111100000 Why? XL2002 SP2 -- | +--Thief_ | Thief, the Number precision in excel is 15 digits -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Thief_" <thief_@hotmail.com> wrote in message news:%23oyY11vOFHA.1096@tk2msftngp13.phx.gbl... > I have formatted a cell as Number, no decimals. If I put in the...

Find last space from the right of text
I need to find the last space from the right of text in a cell and then return the text that is to the right of that last space. Any help is greatly appreciated. Thanks Check out MID(), FIND(), SEARCH(), and RIGHT() functions. Can you provide an example or two of your data? =MID(SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255) On Mon, 24 Jan 2005 09:49:02 -0800, "Michael" <Michael@discussions.microsoft.com> wrote:...

Help sending out large number of similar emails
(New user) I'll be sending out a large number of similar emails and looking for a way to set up a template I can open in Outlook for new messages. These emails will all have the same title and a similar body. Is there a way to open say 50 new emails all with the same title and body? Then I can just change what's needed in the body for that purpose. (I'm using Outlook 2002) Thanks in advance. "FISH" <no@spam.com> wrote in message news:1bb2l.2983$jZ1.2454@flpi144.ffdc.sbc.com... > I'll be sending out a large number of similar emails and looking fo...

PLEASE HELP
PLEASE! - Can someone please tell me how to make an automatic opdated invoice number in Excel In other words I want to get a new number each time I open Excel I thank you on beforehand Look at the replies to your original post 20 min ago -- Don Guillett SalesAid Software donaldb@281.com "Dane" <dane@dane.com> wrote in message news:41d47905$0$15000$9a6e19ea@unlimited.newshosting.com... > PLEASE! - Can someone please tell me how to make an automatic > opdated invoice number in Excel > > In other words I want to get a new number each time I open Excel > &...

Pulling Information from different workbook pages
I have two very large pages in one spreadsheet and I need to pull information from one column from both pages and place them into another page. Can anybody help me! -- RedFive Details? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "RedFive" <Redfive@discussions.microsoft.com> wrote in message news:5382C498-0740-4AD6-BDBA-EE5C6F9DD985@microsoft.com... >I have two very large pages in one spreadsheet and I need to pull >information > from one column from both pages and place them into another page. Can > anybody help me! > ...

Users vs Owners level rights
Hi,I learnt at some point that it s more secure to set the queries rights atowners level rather than grant rights to the table itself. and i have beendoing that, but quite blindly.could someone clarify as to what is the exact benefit of doing this? what ican see that it limits the extent of change a user can do to the table towhatever the query will allow. Is there something besides this too?ThanksRamesh Thanks Arvin. Ramesh "Arvin Meyer [MVP]" <a@m.com> wrote in message news:uYrxsSUcHHA.5052@TK2MSFTNGP05.phx.gbl... > You've got the main reason to secure the tables...

Command bar IDs for Outlook 2007
Hi, I'm am in search of how to find the command bar ID's in Outlook 2007. Can anyone help? Thanks, TKE402 See OutSpy on www.dimastr.com. With that tool you can easily find out all the IDs. -- Best regards Michael Bauer - MVP Outlook : Outlook Categories? Category Manager Is Your Tool : VBOffice Reporter for Data Analysis & Reporting : <http://www.vboffice.net/product.html?pub=6&lang=en> Am Wed, 9 Dec 2009 07:35:02 -0800 schrieb TKE402: > Hi, > > I'm am in search of how to find the command bar ID's in Outlook 2...

Re numbering the row.
Is there a way to re number a row, were row number 1 would move down to the next row to row number 2. Row number 2 would be moved to row number 3 and so for... Thanks, insert a new row at the top,or cut the whole sheet and paste one row down? -- paul remove nospam for email addy! "Javster095" wrote: > Is there a way to re number a row, were row number 1 would move down to the > next row to row number 2. Row number 2 would be moved to row number 3 and so > for... > > Thanks, Your could use the ROW() function in each cell where you want row numbers. -...