Lookup question

[DCC]

New Access user here.
I am looking to setup a simple query in Access.

I have a workbook with seven worksheets. Each worksheet has one column of
numers on it, product codes. Each worksheet covers a different product.

I would like a user to be able to click a button next to the product they
need a code for and Access would go out and grab the next code and display
it. Then Access would need to know NOT to pull that same code again but go
to the next one in the worksheet.

Is this possible?

 

[Jeff Boyce]

I am somewhat confused...

You mention using Access, but refer to worksheets. Worksheets are part of
spreadsheets, not relational databases.

Can you describe your situation a bit more?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DCC]

Indeed I can. I think I deleted the part of my message where I talked about
Excel.
The data I spoke about is stored in an Excel workbook.
This Excel workbook has seven worksheets. Each worksheet has a single
column of data: product registration codes.

I want the Access query to lookup this data as described above in my first
message.

I have imported the data as tables into Access, each worksheet on a separate
table.

 

[Jeff Boyce]

So, you have codes spread out over 7 worksheets. And you imported the codes
as 7 tables in Access.

Why? Do you need 7 different tables? Why were they in 7 different
worksheets to start with? What did that way of organizing them allow you to
do?

I'm still not clear why you'd want a product code "pulled" only once
(whatever that means ... and you and I may mean something totally
different!)?

You've described a "how" (how you are trying to do something). But the
"what" (the something) and the "why" (what business need will get met) are
not yet clear.

More info?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DCC]

A code can only be used once for a product.
So for Product X, there are 10,000 codes that allow product X to work.
But a code can only be used once so what I'd need is that once a particular
code is pulled by a query from the table, that code needs to be deleted from
the table so it can't be pulled again by another user.

I don't need 7 different tables, but it was the only item I could think of.
There are seven lists of product codes so I thought I'd need seven different
tables.

 

[Jeff Boyce]

If there's no underlying business need to discriminate product codes into 7
different "categories", why use 7 different tables?

What is the relationship between products and codes? Can ProductX have more
than one code? Who gets that code? How do you "know" when a code's been
used? For ProductX or ProductY? Can the same code be used for BOTH
ProductX and ProductY?

Still not clear what the business need is... which will greatly affect the
'how'...

Regards

Jeff Boyce
Microsoft Office/Access MVP