excel query

[paul]

Have set up a spreadsheet with a query in to obtain
information from an Access database. All works well
except for one area. One part of the query looks up
values in a column on the database. Thsi colimn has two
values in it- a code number and a price - I want the
price to show in excel but cannot get it to. It always
shows the code number. I have changed the feild in Access
by binding columns 1 and 2 by reversing the lookup
information in the access database but nothing changes
the rresult in excel. Can anyone help please??

 

[JulieD]

Hi Paul

personally, i would make the change in Access - storing two bits of
information in one column in a database is certainly not "best practice" ...
having said that, if you can't change the database could you please post an
example of the data in the column that you're retrieving (just one or lines)
.... also how are you doing the query?

Cheers
JulieD

 

[Guest]

Hi Julie,

Thanks for your reply. I am using the 'get external data'
function in excel to egt the data required and selecting
the relevant information through the dialogue windows.
All data returns fine except this particuylar one.

Within the database there is a lookup column that looks
up a code and price info from anotehr Access database.
This returns the data to this database in one column and
shows both the code and price. The columns are bound in
order to display the price info in the second database.

In the excel query I request the info from the price
column but displays only the code. I have tried to change
the bound columns in the database to see if that would
change anything but have had no luck.

The data in the column is thus:

M100 36.5
M150 42.7

This is just an example. The figure required to display
in the Spreadsheet is the price column which is on the
right but it always displays the left column which is the
code number for the product.

I hope this makes sense for you and look forward to
hearing from you again.

Best Regards

Paul

 

[JulieD]

Hi

what doesn't make sense to me is if the column in Access has both, how come
you're only getting the left bit of data? or are you getting both bits but
can't figure out how to get only the right?

if the second scenario is true then choose to View or Edit the Query in MS
Query
click on the SQL button and edit the column (i'm calling it compound_column)
that you want as follows
SELECT Table1.Compound_Column
to
SELECT RIGHT(Table1.Compound_Column, LEN(Table1.Compound_Column)-3) As Price
.....

Hope this helps
Cheers
JulieD

 

[Jamie Collins]

click on the SQL button and edit the column (i'm calling it compound_column)
that you want as follows
SELECT Table1.Compound_Column
to
SELECT RIGHT(Table1.Compound_Column, LEN(Table1.Compound_Column)-3) As Price

Perhaps better would be:

SELECT
CCUR(MID(compound_column, INSTR(compound_column, ' ')+1))
AS Price ...

Jamie.

--