SQL Results Problem

[Astra]

Hi All

I submitted my schema last time and I think the verbose nature of my query
put people off.

In which case, I have simplified it into the below logic, which will
hopefully make sense.

I have one table (no special indexes or constraints - yet) that holds
different currency prices. I call it STOCKPRICES and a typical example of a
row in the table is as follows:

STOCKID CURRENCYID PRICE
IVP USDollar 50.00
IVP Sterling 23.00
STP Sterling 21.00
etc.....

I have one table (no special indexes or constraints - yet) that holds
different stock language descriptions. I call it STOCKDESCS and a typical
example of a row in the table is as follows:

STOCKID LANGUAGEID DESC
IVP English Invoice Paper
IVP Spanish Inverse Papa
STP English Statement Paper
etc.....

My final table of these 3 (no special indexes or constraints - yet) holds
generic stuff like the weight, whether its a new product, etc and is like my
Base table. I call it STOCK and a typical example of a row in the table is
as follows:

STOCKID LANGUAGEID DESC
IVP English Invoice Paper
IVP Spanish Inverse Papa
STP English Statement Paper
etc.....

My ASP web site gives users the option to see a list of products in
different currencies and languages, but because of the Cartesian (?)
approach of my original query if a particular stock item does have a
different language desc, but not a price in say US Dollars then this product
isn't displayed.

What I wanted is that if the stock item doesn't have a different currency
price (from the default) in the STOCKPRICES table then I put in a converted
amount, which is the default currency price (needs to be extracted) x the
exchange rate (I hold in a Session var) .

Basically my problem is that how do I instruct an Access/MySQL in-line query
to fill in a price on any stock items that don't have a price in the
STOCKPRICES table with a converted price based on the default currency's
price in the STOCKPRICES table?

At present, my query is as follows:

IF cSelCurrency <> cDefCurrency THEN

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
((sp.RRPNETAMOUNT * " & cSelCurrencyRate & ") + (sp.RRPTAXAMOUNT * " &
cSelCurrencyRate & ")) AS RRP, ((sp.NETAMOUNT * " & cSelCurrencyRate & ") +
(sp.TAXAMOUNT * " & cSelCurrencyRate & ")) AS PRICE FROM STOCK s, STOCKDESCS
sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID AND
sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"

ELSE

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
(sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP, (sp.NETAMOUNT + sp.TAXAMOUNT) AS
PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID
AND s.STOCKID=sp.STOCKID AND sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"

END IF

Which is basically a really simple 'if the currency selected isn't the
default currency then do converted prices else get the prices from the
table'. Yet again this is like an all or nothing kludge query, as there may
be 6 out of 10 stock items actually with different currency prices, but my
query is basically saying ignore this and do a conversion because the
default currency is different to the selected one.

To take this example further, I want my query to put the different currency
prices in for the 6 that have them and put a converted price in for the 4
that don't.

Can u help???

Rgds

Robbie

[Bob Barrows [MVP]]

Astra wrote:

> Hi All
>
> I submitted my schema last time and I think the verbose nature of my
> query put people off.
>
> In which case, I have simplified it into the below logic, which will
> hopefully make sense.
>
> I have one table (no special indexes or constraints - yet) that holds
> different currency prices. I call it STOCKPRICES and a typical
> example of a row in the table is as follows:
>
> STOCKID CURRENCYID PRICE
> IVP USDollar 50.00
> IVP Sterling 23.00
> STP Sterling 21.00
> etc.....
>
> I have one table (no special indexes or constraints - yet) that holds
> different stock language descriptions. I call it STOCKDESCS and a
> typical example of a row in the table is as follows:
>
> STOCKID LANGUAGEID DESC
> IVP English Invoice Paper
> IVP Spanish Inverse Papa
> STP English Statement Paper
> etc.....
>
> My final table of these 3 (no special indexes or constraints - yet)
> holds generic stuff like the weight, whether its a new product, etc
> and is like my Base table. I call it STOCK and a typical example of
> a row in the table is as follows:
>
> STOCKID LANGUAGEID DESC
> IVP English Invoice Paper
> IVP Spanish Inverse Papa
> STP English Statement Paper
> etc.....

This looks like the same table as above ... typo?

>
>

Up to here, your description is good. However, you just blew it. What you
should have done at this point is shown us the results you wish to obtain
from the above sample data instead of attempting to describe them. Show us
the desired results in the same manner that you showed us the sample data.

Then you could have shown us what you tried, and the incorrect results you
obtained from that atte mpt. The keyword in the previous statement is
"shown".

Bob Barrows

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Laphan]

Dear Bob

Apologies for the confusion on the tables. I don't quite know how I managed
to do this.

My 1st table is called STOCKPRICES and a typical example of a row in the
table is as follows:

STOCKID CURRENCYID RRPNETAMOUNT RRPTAX NETAMOUNT TAXAMOUNT
IVP USDollar 500.00 87.50
300.00 52.50
IVP Sterling 200.00 35.00
100.00 17.50
STP Sterling 50.00 8.80
25.00 4.00
etc.....

My 3rd table is called STOCK and a typical example of a row in the table is
as follows:

STOCKID PIC THUMBPIC CATID WEIGHT
IVP ivp-big.jpg ivp-thumb.jpg 001 25.50
STP stp-big.jpg stp-thumb.jpg 001 3.50
KGC kgc-big.jpg kgc-thumb.jpg 004 10.00
etc.....

Please note that my tables do use the ISO country codes for such as the lang
and currency ids, I just wanted to write them long hand as Sterling,
USDollar, etc so it made sense what I was using as values.

Taking the above as an example, if the user selects their currency to be US
Dollar even though the web site default is Sterling I wanted my query to
know that IVP has a US Dollar price in the STOCKPRICES table so display
that, but as STP and KGC don't have price entered in the STOCKPRICES table
then use a conversion of the Sterling value * my session stored exchange
rate value. Taking into account that my session stored exchange rate value
is say 1.6, the results should be coming out like the following:

IVP prices come out as:
RRP = $587.50 ($500.00 net and $87.50 tax) << from the STOCKPRICES db
OUR PRICE = $352.50 ($300.00 net and $52.50 tax) << from the STOCKPRICES db

STP prices come out as:
RRP = $94.00 ($80.00 net and $14.00 tax) << calc'd from Sterling price *
session var rate of 1.6
OUR PRICE = $47.00 ($40.00 net and $7.00 tax) << calc'd from Sterling price
* session var rate of 1.6

My current If...Then..Else... query (below) either converts the default
currency values with the exchange rate if the selection is not the default
currency or simply gets the default currency values.

IF cSelCurrency <> cDefCurrency THEN

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
((sp.RRPNETAMOUNT * " & cSelCurrencyRate & ") + (sp.RRPTAXAMOUNT * " &
cSelCurrencyRate & ")) AS RRP, ((sp.NETAMOUNT * " & cSelCurrencyRate & ") +
(sp.TAXAMOUNT * " & cSelCurrencyRate & ")) AS PRICE FROM STOCK s, STOCKDESCS
sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID AND
sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"

ELSE

strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME, sd.SHORTDESC,
(sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP, (sp.NETAMOUNT + sp.TAXAMOUNT) AS
PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID
AND s.STOCKID=sp.STOCKID AND sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"

END IF

Surely it would be impractical for me to do a separate query for each
product to check whether to get a foreign currency price from the
STOCKPRICES table or to produce a convetered amount so how do I modify my
current query to do this double-check practically.

Rgds

Robbie





Bob Barrows [MVP] <reb01501@NOyahoo.SPAMcom> wrote in message
news:eenkZL1NEHA.3988@TK2MSFTNGP09.phx.gbl...
Astra wrote:

> Hi All
>
> I submitted my schema last time and I think the verbose nature of my
> query put people off.
>
> In which case, I have simplified it into the below logic, which will
> hopefully make sense.
>
> I have one table (no special indexes or constraints - yet) that holds
> different currency prices. I call it STOCKPRICES and a typical
> example of a row in the table is as follows:
>
> STOCKID CURRENCYID PRICE
> IVP USDollar 50.00
> IVP Sterling 23.00
> STP Sterling 21.00
> etc.....
>
> I have one table (no special indexes or constraints - yet) that holds
> different stock language descriptions. I call it STOCKDESCS and a
> typical example of a row in the table is as follows:
>
> STOCKID LANGUAGEID DESC
> IVP English Invoice Paper
> IVP Spanish Inverse Papa
> STP English Statement Paper
> etc.....
>
> My final table of these 3 (no special indexes or constraints - yet)
> holds generic stuff like the weight, whether its a new product, etc
> and is like my Base table. I call it STOCK and a typical example of
> a row in the table is as follows:
>
> STOCKID LANGUAGEID DESC
> IVP English Invoice Paper
> IVP Spanish Inverse Papa
> STP English Statement Paper
> etc.....

This looks like the same table as above ... typo?

>
>

Up to here, your description is good. However, you just blew it. What you
should have done at this point is shown us the results you wish to obtain
from the above sample data instead of attempting to describe them. Show us
the desired results in the same manner that you showed us the sample data.

Then you could have shown us what you tried, and the incorrect results you
obtained from that atte mpt. The keyword in the previous statement is
"shown".

Bob Barrows

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Again, a lot of text to wade through (I would have preferred to see the rows
and columns of data you wished your query to return from the sample data
before you started explaining the results), but I think I have the idea.

Firstly, I dug through your initial post to find out what database you're
using and found this: "Access/MySQL". I have no idea how to write a query
that will work in both Access and MySQL. You will need to go elsewhere for
MySQL help. I can show you how to do it in Access.

I do not understand the idea behind storing separate records with different
currencies in the STOCKPRICE table.
Why wouldn't you simply get the values from the Sterling records for each
stock and convert those values to the requested currency? Ignore all records
that aren't entered in your default currency. I'm assuming that every stock
will have a record with Sterling values ...

Bob Barrows

Laphan wrote:

> Dear Bob
>
> Apologies for the confusion on the tables. I don't quite know how I
> managed to do this.
>
> My 1st table is called STOCKPRICES and a typical example of a row in
> the table is as follows:
>
> STOCKID CURRENCYID RRPNETAMOUNT RRPTAX NETAMOUNT
> TAXAMOUNT IVP USDollar 500.00
> 87.50 300.00 52.50
> IVP Sterling 200.00
> 35.00 100.00 17.50
> STP Sterling 50.00
> 8.80
> 25.00 4.00
> etc.....
>
> My 3rd table is called STOCK and a typical example of a row in the
> table is as follows:
>
> STOCKID PIC THUMBPIC CATID WEIGHT
> IVP ivp-big.jpg ivp-thumb.jpg 001
> 25.50
> STP stp-big.jpg stp-thumb.jpg 001 3.50
> KGC kgc-big.jpg kgc-thumb.jpg 004 10.00
> etc.....
>
> Please note that my tables do use the ISO country codes for such as

<snip>.

>
> Taking the above as an example, if the user selects their currency to
> be US Dollar even though the web site default is Sterling I wanted my
> query to know that IVP has a US Dollar price in the STOCKPRICES table
> so display that, but as STP and KGC don't have price entered in the
> STOCKPRICES table then use a conversion of the Sterling value * my
> session stored exchange rate value. Taking into account that my
> session stored exchange rate value is say 1.6, the results should be
> coming out like the following:
>
> IVP prices come out as:
> RRP = $587.50 ($500.00 net and $87.50 tax) << from the STOCKPRICES db
> OUR PRICE = $352.50 ($300.00 net and $52.50 tax) << from the
> STOCKPRICES db
>
> STP prices come out as:
> RRP = $94.00 ($80.00 net and $14.00 tax) << calc'd from Sterling
> price * session var rate of 1.6
> OUR PRICE = $47.00 ($40.00 net and $7.00 tax) << calc'd from
> Sterling price * session var rate of 1.6
>
> My current If...Then..Else... query (below) either converts the
> default currency values with the exchange rate if the selection is
> not the default currency or simply gets the default currency values.
>
> IF cSelCurrency <> cDefCurrency THEN
>
> strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME,
> sd.SHORTDESC, ((sp.RRPNETAMOUNT * " & cSelCurrencyRate & ") +
> (sp.RRPTAXAMOUNT * " & cSelCurrencyRate & ")) AS RRP, ((sp.NETAMOUNT
> * " & cSelCurrencyRate & ") + (sp.TAXAMOUNT * " & cSelCurrencyRate &
> ")) AS PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE
> s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID AND sd.LANGID='" &
> cSelLang & "' AND s.SPECIAL=1"
>
> ELSE
>
> strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME,
> sd.SHORTDESC, (sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP,
> (sp.NETAMOUNT + sp.TAXAMOUNT) AS PRICE FROM STOCK s, STOCKDESCS sd,
> STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID
> AND sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"
>
> END IF
>
> Surely it would be impractical for me to do a separate query for each
> product to check whether to get a foreign currency price from the
> STOCKPRICES table or to produce a convetered amount so how do I
> modify my current query to do this double-check practically.
>
> Rgds
>
> Robbie
>
>
>
>
>
> Bob Barrows [MVP] <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eenkZL1NEHA.3988@TK2MSFTNGP09.phx.gbl...
> Astra wrote:

>> Hi All
>>
>> I submitted my schema last time and I think the verbose nature of my
>> query put people off.
>>
>> In which case, I have simplified it into the below logic, which will
>> hopefully make sense.
>>
>> I have one table (no special indexes or constraints - yet) that holds
>> different currency prices. I call it STOCKPRICES and a typical
>> example of a row in the table is as follows:
>>
>> STOCKID CURRENCYID PRICE
>> IVP USDollar 50.00
>> IVP Sterling 23.00
>> STP Sterling 21.00
>> etc.....
>>
>> I have one table (no special indexes or constraints - yet) that holds
>> different stock language descriptions. I call it STOCKDESCS and a
>> typical example of a row in the table is as follows:
>>
>> STOCKID LANGUAGEID DESC
>> IVP English Invoice Paper
>> IVP Spanish Inverse Papa
>> STP English Statement Paper
>> etc.....
>>
>> My final table of these 3 (no special indexes or constraints - yet)
>> holds generic stuff like the weight, whether its a new product, etc
>> and is like my Base table. I call it STOCK and a typical example of
>> a row in the table is as follows:
>>
>> STOCKID LANGUAGEID DESC
>> IVP English Invoice Paper
>> IVP Spanish Inverse Papa
>> STP English Statement Paper
>> etc.....

>
> This looks like the same table as above ... typo?

>>
>>

> Up to here, your description is good. However, you just blew it. What
> you should have done at this point is shown us the results you wish
> to obtain from the above sample data instead of attempting to
> describe them. Show us the desired results in the same manner that
> you showed us the sample data.
>
> Then you could have shown us what you tried, and the incorrect
> results you obtained from that atte mpt. The keyword in the previous
> statement is "shown".
>
> Bob Barrows
>
> Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Astra]

Hi Bob

Thanks for your patience. I'll the hang of concise posting one day!

In answer to your queries:

1) At present, I'm developing my site using an Access DB for easy and quick
tweaking with the idea being that once done I convert it to a MySQL db to
give me the data access flexibility across http & https and to handle more
users. My ISP offers a far cheaper package to host a MySQL db over a MS SQL
one so this is the reason for this.

2) The reason I am not converting all the prices on the fly is because the
admin wants to put their own foreign currency prices in on certain products
(for many reasons such as the converted price doesn't look atheistically
pleasing, it will cost more if paid in say Euros with bank charges, etc),
but not all.

I want to be able to catch this so that if there is a price for the selected
currency then display this or convert it.

Hope this makes a bit more sense.

Rgds

Robbie


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Oa8Gh55NEHA.2500@TK2MSFTNGP12.phx.gbl...
Again, a lot of text to wade through (I would have preferred to see the rows
and columns of data you wished your query to return from the sample data
before you started explaining the results), but I think I have the idea.

Firstly, I dug through your initial post to find out what database you're
using and found this: "Access/MySQL". I have no idea how to write a query
that will work in both Access and MySQL. You will need to go elsewhere for
MySQL help. I can show you how to do it in Access.

I do not understand the idea behind storing separate records with different
currencies in the STOCKPRICE table.
Why wouldn't you simply get the values from the Sterling records for each
stock and convert those values to the requested currency? Ignore all records
that aren't entered in your default currency. I'm assuming that every stock
will have a record with Sterling values ...

Bob Barrows

Laphan wrote:

> Dear Bob
>
> Apologies for the confusion on the tables. I don't quite know how I
> managed to do this.
>
> My 1st table is called STOCKPRICES and a typical example of a row in
> the table is as follows:
>
> STOCKID CURRENCYID RRPNETAMOUNT RRPTAX NETAMOUNT
> TAXAMOUNT IVP USDollar 500.00
> 87.50 300.00 52.50
> IVP Sterling 200.00
> 35.00 100.00 17.50
> STP Sterling 50.00
> 8.80
> 25.00 4.00
> etc.....
>
> My 3rd table is called STOCK and a typical example of a row in the
> table is as follows:
>
> STOCKID PIC THUMBPIC CATID WEIGHT
> IVP ivp-big.jpg ivp-thumb.jpg 001
> 25.50
> STP stp-big.jpg stp-thumb.jpg 001 3.50
> KGC kgc-big.jpg kgc-thumb.jpg 004 10.00
> etc.....
>
> Please note that my tables do use the ISO country codes for such as

<snip>.

>
> Taking the above as an example, if the user selects their currency to
> be US Dollar even though the web site default is Sterling I wanted my
> query to know that IVP has a US Dollar price in the STOCKPRICES table
> so display that, but as STP and KGC don't have price entered in the
> STOCKPRICES table then use a conversion of the Sterling value * my
> session stored exchange rate value. Taking into account that my
> session stored exchange rate value is say 1.6, the results should be
> coming out like the following:
>
> IVP prices come out as:
> RRP = $587.50 ($500.00 net and $87.50 tax) << from the STOCKPRICES db
> OUR PRICE = $352.50 ($300.00 net and $52.50 tax) << from the
> STOCKPRICES db
>
> STP prices come out as:
> RRP = $94.00 ($80.00 net and $14.00 tax) << calc'd from Sterling
> price * session var rate of 1.6
> OUR PRICE = $47.00 ($40.00 net and $7.00 tax) << calc'd from
> Sterling price * session var rate of 1.6
>
> My current If...Then..Else... query (below) either converts the
> default currency values with the exchange rate if the selection is
> not the default currency or simply gets the default currency values.
>
> IF cSelCurrency <> cDefCurrency THEN
>
> strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME,
> sd.SHORTDESC, ((sp.RRPNETAMOUNT * " & cSelCurrencyRate & ") +
> (sp.RRPTAXAMOUNT * " & cSelCurrencyRate & ")) AS RRP, ((sp.NETAMOUNT
> * " & cSelCurrencyRate & ") + (sp.TAXAMOUNT * " & cSelCurrencyRate &
> ")) AS PRICE FROM STOCK s, STOCKDESCS sd, STOCKPRICES sp WHERE
> s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID AND sd.LANGID='" &
> cSelLang & "' AND s.SPECIAL=1"
>
> ELSE
>
> strSQLQuery = "SELECT s.STOCKID, s.THUMBPIC, sd.STOCKNAME,
> sd.SHORTDESC, (sp.RRPNETAMOUNT + sp.RRPTAXAMOUNT) AS RRP,
> (sp.NETAMOUNT + sp.TAXAMOUNT) AS PRICE FROM STOCK s, STOCKDESCS sd,
> STOCKPRICES sp WHERE s.STOCKID=sd.STOCKID AND s.STOCKID=sp.STOCKID
> AND sd.LANGID='" & cSelLang & "' AND s.SPECIAL=1"
>
> END IF
>
> Surely it would be impractical for me to do a separate query for each
> product to check whether to get a foreign currency price from the
> STOCKPRICES table or to produce a convetered amount so how do I
> modify my current query to do this double-check practically.
>
> Rgds
>
> Robbie
>
>
>
>
>
> Bob Barrows [MVP] <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eenkZL1NEHA.3988@TK2MSFTNGP09.phx.gbl...
> Astra wrote:

>> Hi All
>>
>> I submitted my schema last time and I think the verbose nature of my
>> query put people off.
>>
>> In which case, I have simplified it into the below logic, which will
>> hopefully make sense.
>>
>> I have one table (no special indexes or constraints - yet) that holds
>> different currency prices. I call it STOCKPRICES and a typical
>> example of a row in the table is as follows:
>>
>> STOCKID CURRENCYID PRICE
>> IVP USDollar 50.00
>> IVP Sterling 23.00
>> STP Sterling 21.00
>> etc.....
>>
>> I have one table (no special indexes or constraints - yet) that holds
>> different stock language descriptions. I call it STOCKDESCS and a
>> typical example of a row in the table is as follows:
>>
>> STOCKID LANGUAGEID DESC
>> IVP English Invoice Paper
>> IVP Spanish Inverse Papa
>> STP English Statement Paper
>> etc.....
>>
>> My final table of these 3 (no special indexes or constraints - yet)
>> holds generic stuff like the weight, whether its a new product, etc
>> and is like my Base table. I call it STOCK and a typical example of
>> a row in the table is as follows:
>>
>> STOCKID LANGUAGEID DESC
>> IVP English Invoice Paper
>> IVP Spanish Inverse Papa
>> STP English Statement Paper
>> etc.....

>
> This looks like the same table as above ... typo?

>>
>>

> Up to here, your description is good. However, you just blew it. What
> you should have done at this point is shown us the results you wish
> to obtain from the above sample data instead of attempting to
> describe them. Show us the desired results in the same manner that
> you showed us the sample data.
>
> Then you could have shown us what you tried, and the incorrect
> results you obtained from that atte mpt. The keyword in the previous
> statement is "shown".
>
> Bob Barrows
>
> Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Astra wrote:

> Hi Bob
>
> Thanks for your patience. I'll the hang of concise posting one day!
>
> In answer to your queries:
>
> 1) At present, I'm developing my site using an Access DB for easy and
> quick tweaking with the idea being that once done I convert it to a
> MySQL db to give me the data access flexibility across http & https
> and to handle more users. My ISP offers a far cheaper package to
> host a MySQL db over a MS SQL one so this is the reason for this.

As I said, I can't help you here. There are sufficient syntax differences
between JetSQL and other versions of SQL that it will be highly unlikely
that I can offer a query that will work in both an Access database and
MySQL, especially considering the the fact that proprietary methods will
need to be used to achieve what you are after.

>
> 2) The reason I am not converting all the prices on the fly is
> because the admin wants to put their own foreign currency prices in
> on certain products (for many reasons such as the converted price
> doesn't look atheistically pleasing, it will cost more if paid in say
> Euros with bank charges, etc), but not all.
>
> I want to be able to catch this so that if there is a price for the
> selected currency then display this or convert it.
>
> Hope this makes a bit more sense.
>

Actually it doesn't. You can allow users to enter the data in their own
currency, then convert the data to the default currency before storing it in
the database. If the users need to see the data as it was entered, you can
store the exchange rate used to convert the data in the record, using that
exchange rate instead of the current exchange rate to convert the data to
the requested currency before display. You can give them the choice as to
whether to use the historical exchange rate that was in effect when the data
was entered, or the current exchange rate when doing the conversion. This
will make things so much easier for you.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Astra]

You're really clever you Bob.

Many thanks.

Rgds

Robbie


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:e7ufD$AOEHA.4036@TK2MSFTNGP12.phx.gbl...
Astra wrote:

> Hi Bob
>
> Thanks for your patience. I'll the hang of concise posting one day!
>
> In answer to your queries:
>
> 1) At present, I'm developing my site using an Access DB for easy and
> quick tweaking with the idea being that once done I convert it to a
> MySQL db to give me the data access flexibility across http & https
> and to handle more users. My ISP offers a far cheaper package to
> host a MySQL db over a MS SQL one so this is the reason for this.

As I said, I can't help you here. There are sufficient syntax differences
between JetSQL and other versions of SQL that it will be highly unlikely
that I can offer a query that will work in both an Access database and
MySQL, especially considering the the fact that proprietary methods will
need to be used to achieve what you are after.

>
> 2) The reason I am not converting all the prices on the fly is
> because the admin wants to put their own foreign currency prices in
> on certain products (for many reasons such as the converted price
> doesn't look atheistically pleasing, it will cost more if paid in say
> Euros with bank charges, etc), but not all.
>
> I want to be able to catch this so that if there is a price for the
> selected currency then display this or convert it.
>
> Hope this makes a bit more sense.
>

Actually it doesn't. You can allow users to enter the data in their own
currency, then convert the data to the default currency before storing it in
the database. If the users need to see the data as it was entered, you can
store the exchange rate used to convert the data in the record, using that
exchange rate instead of the current exchange rate to convert the data to
the requested currency before display. You can give them the choice as to
whether to use the historical exchange rate that was in effect when the data
was entered, or the current exchange rate when doing the conversion. This
will make things so much easier for you.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"