Query Criteria looking for circa 270 values (1 Viewer)

jyadayada

Registered User.
Local time
Today, 06:04
Joined
Sep 6, 2018
Messages
44
Whats the easies way to have the query criteria being the equivelent of a vlookup, i.e. pointing to an editable table of values to return other than a string of values in the criteria box itself?


thanks in advance!


I haven't danced with Access in over a decade so on a rapid refresher
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:04
Joined
May 7, 2009
Messages
19,169
you need a form to mimic the criteria box and a table to save the editable value.
 

jyadayada

Registered User.
Local time
Today, 06:04
Joined
Sep 6, 2018
Messages
44
Thanks, I'm probably not being clear, I want to run a query against a single access table, I want all rows against three values in a key field and within those rows all rows against the circa 270 rows from another number field, so for this latter field I'm guessing a dlookup table (this is the editable bit so at a later point i can change the lookup table contents to update the query) I just can't quite get my head aroud the criteria syntax,



its the expression and domain parts i don't quite get, and the criteria part of a dlookup syntax is the content of the dlookup table, , say the pertinent fields are site and stock item, of all the rows in the table, i want 3 out of 3000 sites and within those 3 sites 270 of 1500 stock items, at a later point i want to edit the 270 list so this is where i thought a seperate table containing the 270 but how to write the dlookup syntax in the criteria field of the query?


I hope this makes more sense!


thanks


Jason
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:04
Joined
Jan 23, 2006
Messages
15,364
Jason,

Can you be more specific? An example of a few Sites and StockItems might help clarify your requirement. Are your tables "normalized"?
 

jyadayada

Registered User.
Local time
Today, 06:04
Joined
Sep 6, 2018
Messages
44
So sites are a 4 digit identifier and stockitems a 6 digit code, so my current query has e.g. under site, criteria syntax of 1234 Or 567 Or 3456 but having this kind of syntax in the criteria under stock item 20 times over would be a slog to create and even more to edit so it's in the criteria of stock item that i'm trying to work out how to poin this to a list of values (held in a new table expressly for the purpose) that can be updated easily


so store 1234 might have 12000 of the 15000 potential stock items but i only want 270 of them, and for store 5678 and for store 3456
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:04
Joined
Jan 23, 2006
Messages
15,364
It seems you would have a structure along the lines of

Site----->SiteStockItem<-----StockItem
 

jyadayada

Registered User.
Local time
Today, 06:04
Joined
Sep 6, 2018
Messages
44
No this is a single table query, i need an extract from the 1.5 million lines bringing just those particular results so generating a smaller more workable table



I built it manually by pasting stockitem Or stockitem Or stockitem etc into the criteria rows under the stock item field but this caps out at 255 and i currently want to query 273 but this will increase once i've got this running properly
 

jyadayada

Registered User.
Local time
Today, 06:04
Joined
Sep 6, 2018
Messages
44
OK the table is called tbl_CurrentSites the field i want 3 out of 2500 returning is called Site and the field I want (currently) 273 out of 15000 returning is called StockItem, both of these fileds are numerical, Site is 4 digit and StockItem is 6 digits, I thought i could create an additional helper table with the 23 stock items in called tbl_RelStockItems and that I could increase or decrease this table as necessary
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:04
Joined
Jan 23, 2006
Messages
15,364
I have to go out for a few hours but here is what I'd try (untested but for consideration)

You could have 2 helper tables

SitesReqd and StockItemReqd

where
SitesReqd has 1 field SiteID number PK -fill this with the sites you want to "find"
StockItemReqd has 1 field StockItemId number PK -fill this with the SStockItems you want to "find"

Select * from tbl_CurrentSites where
Site in (Select SiteId from SitesReqd) AND
StockItem in (select stockitemId from StockItemReqd)


NOTE: UNTESTED
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:04
Joined
May 7, 2009
Messages
19,169
i know understand.
you create a table for site (say tblSiteCriteria) and another table for StockItem (tblItemCriteria):



SELECT (yourTable.* FROM yourTable INNER JOIN tblSiteCriteria ON yourTable.SiteID=tblSiteCriteria.SiteID) INNER JOIN tblItemCriteria ON yourTable.StockID=tblItemCriteria.StockID;
 

jyadayada

Registered User.
Local time
Today, 06:04
Joined
Sep 6, 2018
Messages
44
with a little tweaking i got that working (no pk's in the helper tables, just a single field of values in each)
 

jyadayada

Registered User.
Local time
Today, 06:04
Joined
Sep 6, 2018
Messages
44
@arnelgp - i will play with this method also as i need to refresh myself on joins
 

Users who are viewing this thread

Top Bottom