Please HELP - build query to get right report results - PREVIOUS POST SENT BY MISTAKE !

[Guest]

Hi

The DB was not designed efficiently to begin with - I am working with a set of existing tables to provide what the customer's expectation of their reports format to be like and I am finding it quite a challenge

Report layout now looks something like this
==========================================
fax 10
e-mail 50
phone 5
other 2
télécopieur 10
téléphone 6

Query
====
SELECT count(*) AS ReceiptMethodCount, [Receipt Method] FROM Correspondence WHERE [Correspondence Date]>=[Forms]![Reports Form]![DateFirst] And [Correspondence Date]<=[Forms]![Reports Form]![DateLast] GROUP BY [Receipt Method]

Problem
======
'télécopieur' is the french word for 'fax
'téléphone' is the french word for 'phone

Therefore the report should look like : (there are only 4 [Receipt Method] items available as input items
====================================
fax / télécopieur 20
e-mail / courriel 50
phone / téléphone 11
other / autre 2

Without going in to fix the database and create table lookups for these receipt method, I would like hard code in the query to produce the results - but I do not know how I can do so

Does anybody has any suggestions what I can try out ? Thank yo

 

[John Spencer (MVP)]

In my opinion Hardcoding would be a bad idea. You would do better to add a
table with equivalent values and use that. You could use the Switch function to
build a calculated field.

SELECT SWITCH
([Receipt Method] ="Fax", "Fax/Telecopieur",
[Receipt Method] ="Telecopieur", "Fax/Telecopieur",
[Receipt Method] ="Phone", "Phone/Telephone",
[Receipt Method] ="Telephone", "Phone/Telephone",
...) as Method,
Count(*) as CountThem
FROM Correspondence
WHERE [Correspondence Date]>=[Forms]![Reports Form]![DateFirst]
And [Correspondence Date]<=[Forms]![Reports Form]![DateLast]
GROUP BY SWITCH
([Receipt Method] ="Fax", "Fax/Telecopieur",
[Receipt Method] ="Telecopieur", "Fax/Telecopieur",
[Receipt Method] ="Phone", "Phone/Telephone",
[Receipt Method] ="Telephone", "Phone/Telephone",
...)

Alternatively you could write a VBA function to do the conversion and call that
in the SQL statement.

UNTESTED AIRCODE - with no error handling, etc

Public Function fGroupMethod(strIn)

If Len(strIn & vbNullstring) = 0 then
fGroupMethod = strIn
Else
Select Case StrIN
Case "Fax","Telecopieur"
fGroupMethod = "Fax/Telecopieur"
Case "Phone", "TelePhone"
fGroupMethod = "Phone/Telephone"
Case ...

Case Else
fGroupMethod = strIn
End Select

End if

End Function

With that saved in a module, you can call it in your SQL statement or in any
other place you need it. Also, the function can be updated easily if there are
additional Receipt Methods added.

SELECT count(*) AS ReceiptMethodCount,
FGroupMethod([Receipt Method]) as NewGroup
FROM Correspondence
WHERE [Correspondence Date]>=[Forms]![Reports Form]![DateFirst]
And [Correspondence Date]<=[Forms]![Reports Form]![DateLast]
GROUP BY fGroupMethod([Receipt Method])

ALL that said, you are still better off with creating a table of equivalent
values and using that in your query. It will be more efficient and easier to
maintain - your users could even do that without any programming input.

Hi,

The DB was not designed efficiently to begin with - I am working with a set of existing tables to provide what the customer's expectation of their reports format to be like and I am finding it quite a challenge.

Report layout now looks something like this :
===========================================
fax 100
e-mail 500
phone 50
other 20
télécopieur 105
téléphone 60

Query :
=====
SELECT count(*) AS ReceiptMethodCount, [Receipt Method] FROM Correspondence WHERE [Correspondence Date]>=[Forms]![Reports Form]![DateFirst] And [Correspondence Date]<=[Forms]![Reports Form]![DateLast] GROUP BY [Receipt Method]

Problem :
=======
'télécopieur' is the french word for 'fax'
'téléphone' is the french word for 'phone'

Therefore the report should look like : (there are only 4 [Receipt Method] items available as input items)
=====================================
fax / télécopieur 205
e-mail / courriel 500
phone / téléphone 110
other / autre 20

Without going in to fix the database and create table lookups for these receipt method, I would like hard code in the query to produce the results - but I do not know how I can do so ?

Does anybody has any suggestions what I can try out ? Thank you

 

[Guest]

Hi John

Thank you for the suggestions and this is really very helpful to me.

Yes, I know it is better to add a table lookup. Whoever designed the DB obviously did not consider this language thing. I got involved in this project when the DB is a 'working' DB to use. There are about 10 other possible table lookups that could have be designed for but it was not.

Yes, hard coding is a real bad idea. Hands are tied, time is short - I put together a recommendation to the decision makers and they can decide how they want this project to proceed

Meantime, I have 20 reports to hard code (contractual due delivery is Friday

Appreciate your response

Thank yo


----- John Spencer (MVP) wrote: ----

In my opinion Hardcoding would be a bad idea. You would do better to add
table with equivalent values and use that. You could use the Switch function t
build a calculated field

SELECT SWITC
([Receipt Method] ="Fax", "Fax/Telecopieur"
[Receipt Method] ="Telecopieur", "Fax/Telecopieur"
[Receipt Method] ="Phone", "Phone/Telephone"
[Receipt Method] ="Telephone", "Phone/Telephone"
...) as Method
Count(*) as CountThe
FROM Correspondenc
WHERE [Correspondence Date]>=[Forms]![Reports Form]![DateFirst]
And [Correspondence Date]<=[Forms]![Reports Form]![DateLast
GROUP BY SWITC
([Receipt Method] ="Fax", "Fax/Telecopieur"
[Receipt Method] ="Telecopieur", "Fax/Telecopieur"
[Receipt Method] ="Phone", "Phone/Telephone"
[Receipt Method] ="Telephone", "Phone/Telephone"
...

Alternatively you could write a VBA function to do the conversion and call tha
in the SQL statement

UNTESTED AIRCODE - with no error handling, et

Public Function fGroupMethod(strIn)

If Len(strIn & vbNullstring) = 0 then
fGroupMethod = strI
Else
Select Case StrI
Case "Fax","Telecopieur
fGroupMethod = "Fax/Telecopieur
Case "Phone", "TelePhone
fGroupMethod = "Phone/Telephone
Case ..

Case Els
fGroupMethod = strI
End Selec

End i

End Functio

With that saved in a module, you can call it in your SQL statement or in an
other place you need it. Also, the function can be updated easily if there ar
additional Receipt Methods added

SELECT count(*) AS ReceiptMethodCount,
FGroupMethod([Receipt Method]) as NewGrou
FROM Correspondence
WHERE [Correspondence Date]>=[Forms]![Reports Form]![DateFirst]
And [Correspondence Date]<=[Forms]![Reports Form]![DateLast]
GROUP BY fGroupMethod([Receipt Method]

ALL that said, you are still better off with creating a table of equivalen
values and using that in your query. It will be more efficient and easier t
maintain - your users could even do that without any programming input

ym wrote

Hi
The DB was not designed efficiently to begin with - I am working with a set of existing tables to provide what the customer's expectation of their reports format to be like and I am finding it quite a challenge
Report layout now looks something like this

==========================================
fax 10
e-mail 50
phone 5
other 2
télécopieur 10
téléphone 6

Query

====
SELECT count(*) AS ReceiptMethodCount, [Receipt Method] FROM Correspondence WHERE [Correspondence Date]>=[Forms]![Reports Form]![DateFirst] And [Correspondence Date]<=[Forms]![Reports Form]![DateLast] GROUP BY [Receipt Method

Problem

======
'télécopieur' is the french word for 'fax
'téléphone' is the french word for 'phone

Therefore the report should look like : (there are only 4 [Receipt Method] items available as input items

=====================================
fax / télécopieur 205
e-mail / courriel 500
phone / téléphone 110
other / autre 20

Without going in to fix the database and create table lookups for these receipt method, I would like hard code in the query to produce the results - but I do not know how I can do so ?
Does anybody has any suggestions what I can try out ? Thank you