mileage query

[shelka]

Have imported an Excel file into Access ... "Destinations
From" left-side, row headings AND "Desinations To" as
column headings. From each city to each city a mileage
number is listed. How can I form a query where the user
would see a pop-up box that says: ENTER FROM CITY, then,
ENTER TO CITY, then the resulting mileage pops up?

 

[Duane Hookom]

Your data isn't quite normalized but you could:
Create a form that has two combo boxes. The first displays all of the From
cities. The second would need to display the Field Names (this is a type of
combo box row source). Call them cboFrom and cboTo
Then add a button with code (un-tested):
Dim dblMiles as double
dblMiles = DLookup(Me.cboTo,"tblA","[DestinationsFrom] = """ & Me.cboFrom &
"""")
Msgbox "The miles between = " & Format(dblMiles,"0.00")

You may have issues with field names with spaces or punctuation.

 

[shelka]

The structure was imported from excel ... (i.e.)

Athabasca Calgary Edmonton Viking
Acme 278 115 178 101
Lethbridge 301 75 350 377
Viking 234 278 50 0
Wetaskiwin 55 503 72 290

There are over 130 columns across (which will be added to)
and over 350 rows down (which will be added to). From
point A to point B equals the mileage.

The user of the query should be able to simply type in the
city "from" then then the city "to" and retrieve the
mileage between the two places.

Am have great difficulty with this. And, yes, some cites
have "periods" like "St. Paul" and others have two names
with "spaces" like "Fort Saskatchewan".

HELP!

-----Original Message-----
I don't know where the 20 limit comes from. I just tested a table with about
40 fields and they all showed up in the combo box.
What is your actual data structure? Do you have fields with city names? If
so, you should really consider normalizing. If you want to add another city,
you would have to add another field. This is generally not acceptable.

--
Duane Hookom
MS Access MVP

This won't work as the combo box will only allow me to
select 20 "to destinations'. There are hundreds of "from"
and "to" cities ...

-----Original Message-----
Your data isn't quite normalized but you could:
Create a form that has two combo boxes. The first displays all of the From
cities. The second would need to display the Field

Names
(this is a type of

combo box row source). Call them cboFrom and cboTo
Then add a button with code (un-tested):
Dim dblMiles as double
dblMiles = DLookup(Me.cboTo,"tblA","[DestinationsFrom] = """ & Me.cboFrom &
"""")
Msgbox "The miles between = " & Format(dblMiles,"0.00")

You may have issues with field names with spaces or punctuation.


--
Duane Hookom
MS Access MVP


Have imported an Excel file into Access ... "Destinations
From" left-side, row headings AND "Desinations To" as
column headings. From each city to each city a mileage
number is listed. How can I form a query where the user
would see a pop-up box that says: ENTER FROM CITY, then,
ENTER TO CITY, then the resulting mileage pops up?


.

.

 

[Duane Hookom]

I would not do anything with the table in its current structure. Your values
below should be in 16 record not 4.
CityA CityB Mileage
Acme Athabasca 278
Acme Calgary 115
....
I didn't think Access would allow you to have periods in field names.
--
Duane Hookom
MS Access MVP

The structure was imported from excel ... (i.e.)

Athabasca Calgary Edmonton Viking
Acme 278 115 178 101
Lethbridge 301 75 350 377
Viking 234 278 50 0
Wetaskiwin 55 503 72 290

There are over 130 columns across (which will be added to)
and over 350 rows down (which will be added to). From
point A to point B equals the mileage.

The user of the query should be able to simply type in the
city "from" then then the city "to" and retrieve the
mileage between the two places.

Am have great difficulty with this. And, yes, some cites
have "periods" like "St. Paul" and others have two names
with "spaces" like "Fort Saskatchewan".

HELP!

-----Original Message-----
I don't know where the 20 limit comes from. I just tested a table with about
40 fields and they all showed up in the combo box.
What is your actual data structure? Do you have fields with city names? If
so, you should really consider normalizing. If you want to add another city,
you would have to add another field. This is generally not acceptable.

--
Duane Hookom
MS Access MVP

This won't work as the combo box will only allow me to
select 20 "to destinations'. There are hundreds of "from"
and "to" cities ...
-----Original Message-----
Your data isn't quite normalized but you could:
Create a form that has two combo boxes. The first
displays all of the From
cities. The second would need to display the Field Names
(this is a type of
combo box row source). Call them cboFrom and cboTo
Then add a button with code (un-tested):
Dim dblMiles as double
dblMiles = DLookup(Me.cboTo,"tblA","[DestinationsFrom]
= """ & Me.cboFrom &
"""")
Msgbox "The miles between = " & Format(dblMiles,"0.00")

You may have issues with field names with spaces or
punctuation.


--
Duane Hookom
MS Access MVP


Have imported an Excel file into
Access ... "Destinations
From" left-side, row headings AND "Desinations To" as
column headings. From each city to each city a mileage
number is listed. How can I form a query where the user
would see a pop-up box that says: ENTER FROM CITY, then,
ENTER TO CITY, then the resulting mileage pops up?


.

.

 

[Duane Hookom]

I would create the structure that I suggested previously so that you would
have 3 or 4 fields. It is possible to set up an append query like:
To append from first "To" city (Albany)
INSERT INTO tblCity2City (CityA, CityB, Mileage)
SELECT [FromCity], "Albany", [Albany]
FROM tblYourTable;
Run it
To append from second "To" city (Burbank)
INSERT INTO tblCity2City (CityA, CityB, Mileage)
SELECT [FromCity], "Burbank", [Burbank]
FROM tblYourTable;
Do this for all 130 "To" cities.

You could also write code to do all of this automatically. Roger Carlson has
a ton of small sample databases at www.RogersAccessLibrary.com that
demonstrate lots of techniques. One of these shows how to use code to
normalize NormalizeDenormalize2k.mdb (37 KB) ( intermediate )

--
Duane Hookom
MS Access MVP

I'm totalling missing something in the logic here ... if I
already have 350 "From" cities and over 130 "To" cities,
how can I possible make this work with the 255 field
limitation? What structure WOULD work?

-----Original Message-----
You could import the excel sheet and then use a series of queries to build the
new table structure. That way you wouldn't have to retype all the data.

Access (and excel) have a limit of 255 fields (columns), so eventually if you
get enough cities in this table you are going to be forced to change to Duane's
suggested layout.

Your best bet to get the data from the Excel sheet into an Access table would
probably be some VBA to loop through the rows.

Using your suggested structure of City A, City B, and
Mileage, it would mean re-entering all that
information ... over 91,000 cells to be entered
manually ... doesn't make any sense at all. There must be
a way to import the excel spreadsheet and use a query
(even if it means taking out the periods).
-----Original Message-----
I would not do anything with the table in its current
structure. Your values
below should be in 16 record not 4.
CityA CityB Mileage
Acme Athabasca 278
Acme Calgary 115
....
I didn't think Access would allow you to have periods in
field names.
--
Duane Hookom
MS Access MVP


The structure was imported from excel ... (i.e.)

Athabasca Calgary Edmonton Viking
Acme 278 115 178 101
Lethbridge 301 75 350 377
Viking 234 278 50 0
Wetaskiwin 55 503 72 290

There are over 130 columns across (which will be added
to)
and over 350 rows down (which will be added to). From
point A to point B equals the mileage.

The user of the query should be able to simply type in
the
city "from" then then the city "to" and retrieve the
mileage between the two places.

Am have great difficulty with this. And, yes, some cites
have "periods" like "St. Paul" and others have two names
with "spaces" like "Fort Saskatchewan".

HELP!
-----Original Message-----
I don't know where the 20 limit comes from. I just
tested
a table with about
40 fields and they all showed up in the combo box.
What is your actual data structure? Do you have fields
with city names? If
so, you should really consider normalizing. If you want
to add another city,
you would have to add another field. This is generally
not acceptable.

--
Duane Hookom
MS Access MVP


This won't work as the combo box will only allow me
to
select 20 "to destinations'. There are hundreds
of "from"
and "to" cities ...
-----Original Message-----
Your data isn't quite normalized but you could:
Create a form that has two combo boxes. The first
displays all of the From
cities. The second would need to display the Field
Names
(this is a type of
combo box row source). Call them cboFrom and cboTo
Then add a button with code (un-tested):
Dim dblMiles as double
dblMiles = DLookup
(Me.cboTo,"tblA","[DestinationsFrom]
= """ & Me.cboFrom &
"""")
Msgbox "The miles between = " & Format
(dblMiles,"0.00")

You may have issues with field names with spaces or
punctuation.


--
Duane Hookom
MS Access MVP


Have imported an Excel file into
Access ... "Destinations
From" left-side, row headings AND "Desinations
To" as
column headings. From each city to each city a
mileage
number is listed. How can I form a query where the
user
would see a pop-up box that says: ENTER FROM CITY,
then,
ENTER TO CITY, then the resulting mileage pops up?


.



.



.

.