help with looking up in a table - all varaibles

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

Hi,
I have spent 4 days trying to dlookup a value in a table
and I'm getting no where and am desperate for help! The
value floats and I am sure there's an easy way to do this
and I'm not seeing it.
My table has columns of different pipe schedules (20) and
one nominal pipe size. With the knowledge (changes with
each calculation) what my nominal size is and what my pipe
schedule is, I need this value. My form asks for this
nominal pipe and pipe schedule I just don't know how to
have it look up in the correct column, as it changes.
You see, I can't even ask this clearly...
ANY IDEAS????
 
Cindy said:
I have spent 4 days trying to dlookup a value in a table
and I'm getting no where and am desperate for help! The
value floats and I am sure there's an easy way to do this
and I'm not seeing it.
My table has columns of different pipe schedules (20) and
one nominal pipe size. With the knowledge (changes with
each calculation) what my nominal size is and what my pipe
schedule is, I need this value. My form asks for this
nominal pipe and pipe schedule I just don't know how to
have it look up in the correct column, as it changes.
You see, I can't even ask this clearly...
ANY IDEAS????
Click to expand...


It might have helped if you had provided a small example of
the table fields and where you have the "knowledge". As a
wild guess, try something along these lines:

DLookup(txtschedule,"tablename","pipesize=" & txtpipesize)

where txtschedule and txtpipesize are the variables or
textboxes that contain the desired schedule name and nominal
pipe size values.
 
Hi,
I have spent 4 days trying to dlookup a value in a table
and I'm getting no where and am desperate for help! The
value floats and I am sure there's an easy way to do this
and I'm not seeing it.
My table has columns of different pipe schedules (20) and
one nominal pipe size. With the knowledge (changes with
each calculation) what my nominal size is and what my pipe
schedule is, I need this value. My form asks for this
nominal pipe and pipe schedule I just don't know how to
have it look up in the correct column, as it changes.
You see, I can't even ask this clearly...
ANY IDEAS????
Click to expand...

I think the confusion is caused by the fact that YOUR TABLE STRUCTURE
IS WRONG.

DLookUp cannot choose dynamically which field to look in. You should
not have separate *fields* for pipe schedules; you should have
separate *RECORDS*.

Your table should te turned on end - i.e. you should have three fields
in the table, Schedule, Size, and Value, with twenty rows for each
size (rather than one row with twenty fields).
 
Marchall, I tried that already and it didn't work but
thank you...and Marshall, I think you've hit it on, I can
have a separate table depending on pipe schedule, problem
is, the table name will change according to what the
selection is for pipe schedule. Can I have a condition
for this?

Example: Dlookup("[Nominal Pipe]","Form![Pipe
Schedule]","[Type of Pipe]"...if my Pipe Schedule is the
name of the table and this changes depending on Pipe
Schedule name.

I really wish I could be more clear but you can't draw out
a table here.

I am trying to build a pump calculation program on access,
i know this is crazy, but no one has done this type of
program on a data base, and i thought it could be a fun
tool.

My first column is nominal pipe, second is Type of Pipe -
both these have to be specified...then the following
columns ARE type of pipes, (example sch 10s, ID, etc). I
can perhaps split these each up into a table each? I
still have two unknowns for a table look up. I did this
SO EASILY with a Vlookup in excel. There is no Vlookup in
Access though.

Thanks for your help/time.
Cindy
 
Marchall, I tried that already and it didn't work but
thank you...and Marshall, I think you've hit it on, I can
have a separate table depending on pipe schedule, problem
is, the table name will change according to what the
selection is for pipe schedule. Can I have a condition
for this?

Example: Dlookup("[Nominal Pipe]","Form![Pipe
Schedule]","[Type of Pipe]"...if my Pipe Schedule is the
name of the table and this changes depending on Pipe
Schedule name.

I really wish I could be more clear but you can't draw out
a table here.

I am trying to build a pump calculation program on access,
i know this is crazy, but no one has done this type of
program on a data base, and i thought it could be a fun
tool.

My first column is nominal pipe, second is Type of Pipe -
both these have to be specified...then the following
columns ARE type of pipes, (example sch 10s, ID, etc). I
can perhaps split these each up into a table each? I
still have two unknowns for a table look up. I did this
SO EASILY with a Vlookup in excel. There is no Vlookup in
Access though.
Click to expand...

There is a DLookUp but THAT IS NOT THE PROBLEM.

Excel is a spreadsheet, a good one.

Access is a relational database.

THEY ARE DIFFERENT, and they require different thinking and a
different mindset.

I don't know enough about pipe to make any real suggestions, but I do
know that you CAN create a normalized table which will do this
calculation for you in a simple Query. What you need in the table is
the information you need - in two fields, or three fields, or five, or
whatever it takes - to identify the data that you want. You can then
use a Query with criteria on those fields to find the record
containing the information that you want.

Please explain as to a dummy who's never bought pipe what the options
are and how they determine the price. 10s and ID means *absolutely
nothing* to me, so you'll need to clarify!
 
Thank you for answering me again John,

Disclaimer here, I know it is unorthadox to do what i'm
doing in a database, but that is why I am doing it...I
want to see it done. To see how user friendly it'll be.

I have the nominal pipe diameter, this is the size of the
pipe that you would 'refer' to the pipe as, it is what it
measures say, inside....you have 10 inches, 12, 14, 18,
etc, etc. The pipe schedule is what kind of pipe you are
choosing. Schedule ID, will give you an outer diameter of
10.16 and Sch 10S, will give you 10.54 inches. These are
examples, there are many types of schedules.

In my form, the person chooses the nominal diameter and
then the schedule they would like. The answer to this,
the actual pipe size, is used in my next equations on this
form - figuring the velocity of how fast the liquid will
go, for example. Therefore the actual pipe diameter is
important.

Thus far, I have been trying to ask the database to look
up the pipe schedule (floating value, but answered in the
form already),this is the field I want looked up but it
moves and the nominal pipe diameter, this field stays
there but the row you would go to changes depending.

It seems like there would be a simple way?

I am trying to think differently here. I was so proud of
myself for being able to write visual macros in basic and
vlookup tables, but now I am graduating to the access
world, do something my bosses haven't done ;)

Let me know if I'm clear, I can try to explain better if
you wish.

Also, have access programs been written for palm pilots?
Something a guy (or girl) can carry around a plant with
them, figuring stuff out with my forms? Just a thought.

Thanks again,
Cindy
-----Original Message-----
Marchall, I tried that already and it didn't work but
thank you...and Marshall, I think you've hit it on, I can
have a separate table depending on pipe schedule, problem
is, the table name will change according to what the
selection is for pipe schedule. Can I have a condition
for this?

Example: Dlookup("[Nominal Pipe]","Form![Pipe
Schedule]","[Type of Pipe]"...if my Pipe Schedule is the
name of the table and this changes depending on Pipe
Schedule name.

I really wish I could be more clear but you can't draw out
a table here.

I am trying to build a pump calculation program on access,
i know this is crazy, but no one has done this type of
program on a data base, and i thought it could be a fun
tool.

My first column is nominal pipe, second is Type of Pipe -
both these have to be specified...then the following
columns ARE type of pipes, (example sch 10s, ID, etc). I
can perhaps split these each up into a table each? I
still have two unknowns for a table look up. I did this
SO EASILY with a Vlookup in excel. There is no Vlookup in
Access though.
Click to expand...

There is a DLookUp but THAT IS NOT THE PROBLEM.

Excel is a spreadsheet, a good one.

Access is a relational database.

THEY ARE DIFFERENT, and they require different thinking and a
different mindset.

I don't know enough about pipe to make any real suggestions, but I do
know that you CAN create a normalized table which will do this
calculation for you in a simple Query. What you need in the table is
the information you need - in two fields, or three fields, or five, or
whatever it takes - to identify the data that you want. You can then
use a Query with criteria on those fields to find the record
containing the information that you want.

Please explain as to a dummy who's never bought pipe what the options
are and how they determine the price. 10s and ID means *absolutely
nothing* to me, so you'll need to clarify!


.
Click to expand...
 
Thus far, I have been trying to ask the database to look
up the pipe schedule (floating value, but answered in the
form already),this is the field I want looked up but it
moves and the nominal pipe diameter, this field stays
there but the row you would go to changes depending.
Click to expand...

I really don't understand. Maybe I'm just too locked into Access
jargon but it sounds like you want a Query with three criteria - one
for each field; you could base a Form on the query and display that
value, or you could use the query to return the value to some program
which uses that value in a calculation. A Query can have multiple
criteria, three is a piece of cake.
It seems like there would be a simple way?
Click to expand...

I'm sure there is but since I can't understand what you mean by "the
field stays there but the row you would go to changes" simply doesn't
communicate to me! If you have the criteria, you should get only one
row - the one row that matches those criteria.
I am trying to think differently here. I was so proud of
myself for being able to write visual macros in basic and
vlookup tables, but now I am graduating to the access
world, do something my bosses haven't done ;)
Click to expand...

Excel is VERY DIFFERENT from Access; SQL queries are VERY DIFFERENT
from visual macros. It may be that you're trying to jam Access into a
box of the wrong shape!
Let me know if I'm clear, I can try to explain better if
you wish.

Also, have access programs been written for palm pilots?
Something a guy (or girl) can carry around a plant with
them, figuring stuff out with my forms? Just a thought.
Click to expand...

I'm not an expert with Palms but AFAIK no, Access isn't available.
There are some other database tools which can be interfaced with
Access databases though. You'll need to ask around the support groups
for that hardware for details because I simply don't know!
 
Back
Top