query which selects data at an intersection

[William McNeill]

I am using Access 2007.
I am trying to create a query that will select a particular cell at an
intersection of a particular row and a particular field name. In my query,
the first field contains the information to pick the correct record out of a
table based on the first column in that table which is a primary key field.
The second column in the query contains data which will match the field name
of the same table that I would like to return. The data that is at the
intersection of the row and field name that the query finds is what I would
like to be in the 3rd row of the query. Is this possible? Thanks!!!!

 

[vanderghast]

yes, but far from being optimal: in general you have to know the table AND
the fields you need to seek for the execution plan to be optimal.



The easier way is to use a DLookup:

DLookup( FNWQHFTBR, "TableName", "PrimaryKeyFieldName=" &
PrimaryKeyValueIdentifyingTheRecord)


where FNQWHFTBR is the Field Name, Without Quotes around it, Which Hold, as
value, the Field name To Be Retreived.


Example (as a query, in SQL view):


SELECT DLookup( [Enter the field name you want], "TableNameHere", "Pk="
& [enter the primary key value for the record you seek a value ] )



That works only with Jet (MS SQL Server will bark about an incomplete SELECT
not having a FROM clause)... well, in addition to the fact that DLookup is a
VBA function, that is.



Vanderghast, Access MVP

 

[Jerry Whittle]

Step away from the keyboard! You are trying to "commit spreadsheet". ;-) For
one thing, databases do not have cells.

Sounds like your table has serious normalization problems and won't work
correctly in a relational database. If your field names have 'meaning' then
the table structure is wrong. For example if you have field names like
January, February, March, etc., you'll never get a query to run efficiently
or even at all.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Duane Hookom]

I am having trouble understanding why you would need to do this. However,
assuming you have a table like:
tblEmployees
===============
empEmpID
empFName
empLName
empDept

You could create a union query like:
SELECT empEmpID, "First Name" as Fld, empFName as TheValue
FROM tblEmployees
UNION ALL
SELECT empEmpID, "Last Name", empLName
FROM tblEmployees
UNION ALL
SELECT empEmpID, "Department", empDept
FROM tblEmployees;

Then your query could select the empEmpID to find a particular record and
the Fld to find the value for the field.

 

[John Spencer MVP]

IF SomeValueYouSpecify is a number then you might try.
SELECT FirstField, SecondField
, DLookup(SecondField,"SomeTable","FirstField =" & SomeValueYouSpecify) as
TheResult
FROM SomeTable
WHERE FirstField = SomeValueYouSpecify

If SomeValueYouSpecify is text then you need to add in text delimiters.
SELECT FirstField, SecondField
, DLookup(SecondField,"SomeTable","FirstField =""" & SomeValueYouSpecify &
"""") as TheResult
FROM SomeTable
WHERE FirstField = SomeValueYouSpecify

If SomeValueYouSpecify is a date then you need to add in date delimiters
SELECT FirstField, SecondField
, DLookup(SecondField,"SomeTable","FirstField =#" & SomeValueYouSpecify & "#")
as TheResult
FROM SomeTable
WHERE FirstField = SomeValueYouSpecify

Another option is to use the switch function to return the value
SELECT FirstField, SecondField
, Switch(SecondField = "field3",Field3,SecondField="Field4",Field4,
SecondField="Field5",Field5) as TheResult
FROM SomeTable
WHERE FirstField = SomeValueYouSpecify

The need to do something like this makes me wonder about your data structure.
I would suspect that your data structure is not correctly set up for Access.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[William McNeill]

Thanks everyone for your help. I might be over my head here, and be trying to
do something that can't be done. A little more detail on what I am trying to
do.

I have 2 tables, one called "Customers" and one called "Suppliers." I broker
deals between the companies in these 2 tables. Huge tables. I have a query
that returns matches when "Suppliers" inventory matches "Customers" needs.
This query is called "Match" and contains in each record what the item that
matched was and the name and address of both the supplier and customer.

I have a 3rd table called "Miles." It is set up like a mileage chart like
you might have seen before on road atlases. In this table I have distances
between "grids" that customers and suppliers are in. I use this to figure
freight. This is what this table looks like:
3184 3387 3478 3482 3484
3184 0 317 493 321 249
3387 317 0 588 355 257
3478 493 588 0 274 438
3482 321 355 274 0 160
3484 249 257 438 160 0

It is much larger than this, 98 records and fields to be exact. The top row
that you see above is field names, and the left row it the primary key. These
are both grids of customers and supplier locations. The information in the
middle is the miles between the 2 grids.

In the query, "Match," every record contains the grid of the supplier and
the grid of the customer. I would like to create another field which returns
the number of miles between the 2 grids by looking up the supplier grid
across the field names at the top and looking up the customer grid on the
primaryID and returning the value that is found at the intersection of the
two. Is this possible? Thanks again!!!

 

[vanderghast]

Have a table with three columns:

miles
Loc1 Loc2 Milleage
3184 3387 317
3184 3478 483
...


It is up to you to store the symetry or not (maybe from Loc1 to Loc2 is a
different milleage than from Loc2 to Loc1). I assume it is not dyssimetric,
so, only 'half' the records are needed.


SELECT FromLoc, UpToLoc, Milleage
FROM miles
WHERE Loc1 IN(FromLoc, UpToLoc)
AND Loc2 IN(FromLoc, UpToLoc)



would return the milleage given the two parameters FromLoc and UpToLoc



Vanderghast, Access MVP

 

[William McNeill]

I guess I could re-arrange it into 3 columns. If I did though, the table
would have close to 10,000 records, and could possible grow. Any issues with
that many records?

 

[vanderghast]

Nope, add indexes on the two locations fields, though (one index on each
single field, and, why not, a no dup index on the pair of these two fields,
to be sure you don't have two different milleage for the same from-to).
Remember that FROM is a reserved word, so don't use it as field name.

Note that, actually, for N destinations, you would have N records, each
having 1 integer and N float, and will be limited to 255 fields, none the
less. So you would have a maximum value of N of 254.

With the proposed table with 3 columns, you will have N*(N-1) /2 records,
each having 2 integers + 1 float. Assuming a float requires four times the
space of an integer:

Size of a record, old table: 1 + 4N
Size of a record, new table: 6

Number of records, old: N
Number of records, new: 0.5 * N*(N-1)




so the new design requires less space, 3*N*N - 3*N versus 4*N*N + N


and have not hard limit (except on the size of the whole database itself).


(Adding indexes add some required space).


Vanderghast, Access MVP

 

[William McNeill]

Works great, thanks!!!

Nope, add indexes on the two locations fields, though (one index on each
single field, and, why not, a no dup index on the pair of these two fields,
to be sure you don't have two different milleage for the same from-to).
Remember that FROM is a reserved word, so don't use it as field name.

Note that, actually, for N destinations, you would have N records, each
having 1 integer and N float, and will be limited to 255 fields, none the
less. So you would have a maximum value of N of 254.

With the proposed table with 3 columns, you will have N*(N-1) /2 records,
each having 2 integers + 1 float. Assuming a float requires four times the
space of an integer:

Size of a record, old table: 1 + 4N
Size of a record, new table: 6

Number of records, old: N
Number of records, new: 0.5 * N*(N-1)




so the new design requires less space, 3*N*N - 3*N versus 4*N*N + N


and have not hard limit (except on the size of the whole database itself).


(Adding indexes add some required space).


Vanderghast, Access MVP