Rename 3 values from tables field 'c', 'w' & ' ' to show 'courier','web' & 'post' - i'm a bit rusty.

P

pilch74

Please help I'm absolutely stuck on this one, and I need assistance
pretty badly. I'm issuing a query whereby my order source field has 3
possible values, either c w or blank. But in this case blank isn't
null it's just the 3rd option, phones.

c = cog
w = web
blank = phone.

This is my query so far.

SELECT tblOrderTrans.clubno, Count(tblOrderTrans.orditem) AS [number
of orders], Nz([tblOrderTrans.ordsrc],"P") AS [order type],
Sum(tblOrderTrans.ordvalue) AS SumOfordvalue
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordpremprodflag)<>"P") AND
((Year([orddate]))=2007))
GROUP BY tblOrderTrans.clubno, Nz([tblOrderTrans.ordsrc],"P")
HAVING (((Count(tblOrderTrans.orditem))<>8113));

I've looked at creating a new lookup table just to provide full names
for these order sources but when I link the tables and run the report
the Nulls just don't show up - sure there's an exaplanation for that
too..

Look forward to hearing from you (or anyone).

Regards,

Rich.
 
P

pilch74

Please help I'm absolutely stuck on this one, and I need assistance
pretty badly. I'm issuing a query whereby my order source field has 3
possible values, either c w or blank. But in this case blank isn't
null it's just the 3rd option, phones.

c = cog
w = web
blank = phone.

This is my query so far.

SELECT tblOrderTrans.clubno, Count(tblOrderTrans.orditem) AS [number
of orders], Nz([tblOrderTrans.ordsrc],"P") AS [order type],
Sum(tblOrderTrans.ordvalue) AS SumOfordvalue
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordpremprodflag)<>"P") AND
((Year([orddate]))=2007))
GROUP BY tblOrderTrans.clubno, Nz([tblOrderTrans.ordsrc],"P")
HAVING (((Count(tblOrderTrans.orditem))<>8113));

I've looked at creating a new lookup table just to provide full names
for these order sources but when I link the tables and run the report
the Nulls just don't show up - sure there's an exaplanation for that
too..

Look forward to hearing from you (or anyone).

Regards,

Rich.
Click to expand...

I must just add that UPDATING the source table is NOT an option.
 
K

KARL DEWEY

when I link the tables and run the report the Nulls just don't show up
Did you use a LEFT JOIN in the query for the report?
 
P

pilch74

Did you use a LEFT JOIN in the query for the report?
--
KARL DEWEY
Build a little - Test a little

[email protected] said:
Please help I'm absolutely stuck on this one, and I need assistance
pretty badly. I'm issuing a query whereby my order source field has 3
possible values, either c w or blank. But in this case blank isn't
null it's just the 3rd option, phones.
Click to expand...
c = cog
w = web
blank = phone.
Click to expand...
This is my query so far.
Click to expand...
SELECT tblOrderTrans.clubno, Count(tblOrderTrans.orditem) AS [number
of orders], Nz([tblOrderTrans.ordsrc],"P") AS [order type],
Sum(tblOrderTrans.ordvalue) AS SumOfordvalue
FROM tblOrderTrans
WHERE (((tblOrderTrans.ordpremprodflag)<>"P") AND
((Year([orddate]))=2007))
GROUP BY tblOrderTrans.clubno, Nz([tblOrderTrans.ordsrc],"P")
HAVING (((Count(tblOrderTrans.orditem))<>8113));
Click to expand...
I've looked at creating a new lookup table just to provide full names
for these order sources but when I link the tables and run the report
the Nulls just don't show up - sure there's an exaplanation for that
too..
Click to expand...
Look forward to hearing from you (or anyone).

Rich.
Click to expand...
Click to expand...

I've had assistance from another site called Sitepoint.com.

And this does EXACTLY what I want it to do.

SELECT clubno
, IIF(ordsrc = 'c','cog',
IIF(ordsrc = 'w','web',
IIF(ordsrc = ' ','phone',
IIF(ordsrc = '','phone',
IIF(ISNULL(ordsrc),'phone','summat else'))))) AS [order type]
, SUM(ordvalue) AS TotalOrderValues
FROM tblOrderTrans
WHERE orditem <> '8113'
AND ordpremprodflag <> 'P'
AND YEAR(orddate) = 2007
GROUP
BY clubno
, IIF(ordsrc = 'c','cog',
IIF(ordsrc = 'w','web',
IIF(ordsrc = ' ','phone',
IIF(ordsrc = '','phone',
IIF(ISNULL(ordsrc),'phone','summat else')))))

Thanks for eplying Karl.

Regards,

Rich.
 
Top