Design and layout for Order Analysis?

[CW]

I want to analyse our inquiries/orders conversions, by country, for a
selected period.
From my Inquiries table I have taken the fields Ref, InquiryDate, Country,
Status.
I created several queries as follows:
1. Uses those 4 fields with a FromDate - ToDate parameter on InquiryDate,
and a country specified in the criteria row for the Country column.
2. Takes the Ref field from the above, and Groups by Count. This gives me
the total number of inquiries for the period entered, for the specified
country.
3. As per (1) but using "Booked" in the criteria of Status.
4. Takes the Ref field from the above, and Groups by Count. This gives me
the total number of bookings for the period entered, for the specified
country.

5. Then I created one final query taking the Ref count from (2) and the Ref
count from (4) and added an expression column to give me the percentage
conversion ratio, orders to inquiries. Problem: whatever I do, in this last
query the Count value for inquiries (generated in (2)) does not display. Am I
trying to do too many separate calculations?

As an alternative I created a form and used DLookUp to pull the results of
the two Count queries into controls and then made a calculated control to
display the percentage. This works kind of OK, in that all the numbers pull
through. However, it will not use the date parameter which is an essential
part of the underlying queries. It only works if I remove the parameter.

I am sure this is not a difficult bit of analysis to do and there's probably
a far better/simpler way of doing it, so could somebody please give me
suggestions either (i) why the conversions query won't display one of the
Count figures or (ii) how to design a form that applies a date parameter to
the source queries or (iii) some completely different way of doing all
this??!!

Many thanks
CW

 

[S.Clark]

If I had to guess, I'd say you're having an issue with Nulls, as this is a
standard problem when something 'doesn't show'.

So, trying peppering your queries with the Nz() function to see if it shows.

 

[CW]

Ken -
That was brilliant - especially the ability to do it all with just one
query. I really must try to do more using SQL rather than simply the wizard
to create queries, I can see that it allows much more control and flexibility.
Many thanks
CW

You should be able to do it with a single query:

To return the total inquiries per county within the data range:

PARAMETERS [FromDate] DATETIME,
[ToDate] DATETIME;
SELECT Country, COUNT(*) AS InquiriesReceived,
SUM(IIF(Status = "Booked", 1,0)) AS BookingsReceived,
BookingsReceived/InquiriesReceived*100 AS PercentBooked
FROM Inquiries
WHERE InquiryDate >= [FromDate]
AND InquiryDate < DATEADD(""d",1,[ToDate])
GROUP BY Country;

A few points to note:

1. Its always a good idea to declare date/time parameters to avoid any risk
of the value entered being misinterpreted as an arithmetical expression.

2. The method of defining the date range as on or before the start date and
before the day following the end date is more reliable than a BETWEEN….AND
operation as it caters for any dates which might inadvertently include a non-
zero time of day element.

3. The bookings total is obtained by summing the return value of an
expression which returns 1 or o. Summing the ones is the same as counting
the rows where Status = 'Booked'.

4.. You should be able to reference the computed BookingsReceived and
InquiriesReceived columns by name to compute the percentage of inquiries
booked. If you have any difficulties with this, however, just repeat the
original expressions:

SUM(IIF(Status = "Booked", 1,0))/COUNT(*)*100 AS PercentBooked

Ken Sheridan
Stafford, England

I want to analyse our inquiries/orders conversions, by country, for a
selected period.
From my Inquiries table I have taken the fields Ref, InquiryDate, Country,
Status.
I created several queries as follows:
1. Uses those 4 fields with a FromDate - ToDate parameter on InquiryDate,
and a country specified in the criteria row for the Country column.
2. Takes the Ref field from the above, and Groups by Count. This gives me
the total number of inquiries for the period entered, for the specified
country.
3. As per (1) but using "Booked" in the criteria of Status.
4. Takes the Ref field from the above, and Groups by Count. This gives me
the total number of bookings for the period entered, for the specified
country.

5. Then I created one final query taking the Ref count from (2) and the Ref
count from (4) and added an expression column to give me the percentage
conversion ratio, orders to inquiries. Problem: whatever I do, in this last
query the Count value for inquiries (generated in (2)) does not display. Am I
trying to do too many separate calculations?

As an alternative I created a form and used DLookUp to pull the results of
the two Count queries into controls and then made a calculated control to
display the percentage. This works kind of OK, in that all the numbers pull
through. However, it will not use the date parameter which is an essential
part of the underlying queries. It only works if I remove the parameter.

I am sure this is not a difficult bit of analysis to do and there's probably
a far better/simpler way of doing it, so could somebody please give me
suggestions either (i) why the conversions query won't display one of the
Count figures or (ii) how to design a form that applies a date parameter to
the source queries or (iii) some completely different way of doing all
this??!!

Many thanks
CW

--
Message posted via AccessMonster.com


.

 

[mo]

Ken -
That was brilliant - especially the ability to do it all with just one
query. I really must try to do more using SQL rather than simply the
wizard
to create queries, I can see that it allows much more control and
flexibility.
Many thanks
CW

You should be able to do it with a single query:

To return the total inquiries per county within the data range:

PARAMETERS [FromDate] DATETIME,
[ToDate] DATETIME;
SELECT Country, COUNT(*) AS InquiriesReceived,
SUM(IIF(Status = "Booked", 1,0)) AS BookingsReceived,
BookingsReceived/InquiriesReceived*100 AS PercentBooked
FROM Inquiries
WHERE InquiryDate >= [FromDate]
AND InquiryDate < DATEADD(""d",1,[ToDate])
GROUP BY Country;

A few points to note:

1. Its always a good idea to declare date/time parameters to avoid any
risk
of the value entered being misinterpreted as an arithmetical expression.

2. The method of defining the date range as on or before the start date
and
before the day following the end date is more reliable than a
BETWEEN….AND
operation as it caters for any dates which might inadvertently include a
non-
zero time of day element.

3. The bookings total is obtained by summing the return value of an
expression which returns 1 or o. Summing the ones is the same as
counting
the rows where Status = 'Booked'.

4.. You should be able to reference the computed BookingsReceived and
InquiriesReceived columns by name to compute the percentage of inquiries
booked. If you have any difficulties with this, however, just repeat the
original expressions:

SUM(IIF(Status = "Booked", 1,0))/COUNT(*)*100 AS PercentBooked

Ken Sheridan
Stafford, England

I want to analyse our inquiries/orders conversions, by country, for a
selected period.
From my Inquiries table I have taken the fields Ref, InquiryDate,
Country,
Status.
I created several queries as follows:
1. Uses those 4 fields with a FromDate - ToDate parameter on
InquiryDate,
and a country specified in the criteria row for the Country column.
2. Takes the Ref field from the above, and Groups by Count. This gives
me
the total number of inquiries for the period entered, for the specified
country.
3. As per (1) but using "Booked" in the criteria of Status.
4. Takes the Ref field from the above, and Groups by Count. This gives
me
the total number of bookings for the period entered, for the specified
country.

5. Then I created one final query taking the Ref count from (2) and the
Ref
count from (4) and added an expression column to give me the percentage
conversion ratio, orders to inquiries. Problem: whatever I do, in this
last
query the Count value for inquiries (generated in (2)) does not display.
Am I
trying to do too many separate calculations?

As an alternative I created a form and used DLookUp to pull the results
of
the two Count queries into controls and then made a calculated control
to
display the percentage. This works kind of OK, in that all the numbers
pull
through. However, it will not use the date parameter which is an
essential
part of the underlying queries. It only works if I remove the parameter.

I am sure this is not a difficult bit of analysis to do and there's
probably
a far better/simpler way of doing it, so could somebody please give me
suggestions either (i) why the conversions query won't display one of
the
Count figures or (ii) how to design a form that applies a date parameter
to
the source queries or (iii) some completely different way of doing all
this??!!

Many thanks
CW

--
Message posted via AccessMonster.com


.

 

[De Jager]

Ken -
That was brilliant - especially the ability to do it all with just one
query. I really must try to do more using SQL rather than simply the
wizard
to create queries, I can see that it allows much more control and
flexibility.
Many thanks
CW

You should be able to do it with a single query:

To return the total inquiries per county within the data range:

PARAMETERS [FromDate] DATETIME,
[ToDate] DATETIME;
SELECT Country, COUNT(*) AS InquiriesReceived,
SUM(IIF(Status = "Booked", 1,0)) AS BookingsReceived,
BookingsReceived/InquiriesReceived*100 AS PercentBooked
FROM Inquiries
WHERE InquiryDate >= [FromDate]
AND InquiryDate < DATEADD(""d",1,[ToDate])
GROUP BY Country;

A few points to note:

1. Its always a good idea to declare date/time parameters to avoid any
risk
of the value entered being misinterpreted as an arithmetical expression.

2. The method of defining the date range as on or before the start date
and
before the day following the end date is more reliable than a
BETWEEN….AND
operation as it caters for any dates which might inadvertently include a
non-
zero time of day element.

3. The bookings total is obtained by summing the return value of an
expression which returns 1 or o. Summing the ones is the same as
counting
the rows where Status = 'Booked'.

4.. You should be able to reference the computed BookingsReceived and
InquiriesReceived columns by name to compute the percentage of inquiries
booked. If you have any difficulties with this, however, just repeat the
original expressions:

SUM(IIF(Status = "Booked", 1,0))/COUNT(*)*100 AS PercentBooked

Ken Sheridan
Stafford, England

I want to analyse our inquiries/orders conversions, by country, for a
selected period.
From my Inquiries table I have taken the fields Ref, InquiryDate,
Country,
Status.
I created several queries as follows:
1. Uses those 4 fields with a FromDate - ToDate parameter on
InquiryDate,
and a country specified in the criteria row for the Country column.
2. Takes the Ref field from the above, and Groups by Count. This gives
me
the total number of inquiries for the period entered, for the specified
country.
3. As per (1) but using "Booked" in the criteria of Status.
4. Takes the Ref field from the above, and Groups by Count. This gives
me
the total number of bookings for the period entered, for the specified
country.

5. Then I created one final query taking the Ref count from (2) and the
Ref
count from (4) and added an expression column to give me the percentage
conversion ratio, orders to inquiries. Problem: whatever I do, in this
last
query the Count value for inquiries (generated in (2)) does not display.
Am I
trying to do too many separate calculations?

As an alternative I created a form and used DLookUp to pull the results
of
the two Count queries into controls and then made a calculated control
to
display the percentage. This works kind of OK, in that all the numbers
pull
through. However, it will not use the date parameter which is an
essential
part of the underlying queries. It only works if I remove the parameter.

I am sure this is not a difficult bit of analysis to do and there's
probably
a far better/simpler way of doing it, so could somebody please give me
suggestions either (i) why the conversions query won't display one of
the
Count figures or (ii) how to design a form that applies a date parameter
to
the source queries or (iii) some completely different way of doing all
this??!!

Many thanks
CW

--
Message posted via AccessMonster.com


.

 

[joelgeraldine]

gghjkjklll