Doubles in Query

[GATJA]

I have another problem with a query I am doing today. When I run the query it brings up everything I need, but 5 of the Promo#'s are the same, but they have different # of Executed in them. They should not be like this, they should be together in 1 Promo#, not broken into 2. Here is my SQL code:

SELECT [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo AS [Executed Demos], IIf([Promo Executed]![CountOfPromoNo]=0,0,[Promo Executed]![CountOfPromoNo]/[Promo count]![# of Demos]) AS Percentage
FROM [Promo count] INNER JOIN [Promo Executed] ON [Promo count].PromoNo = [Promo Executed].PromoNo
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo;

Its taken from 2 queries Promo count and Promo Executed.

 

[Access2000_JS1]

SELECT [Promo count].PromoNo as PCPN,[Promo Executed].PromoNo as PEPN, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo AS [Executed Demos], IIf([Promo Executed]![CountOfPromoNo]=0,0,[Promo Executed]![CountOfPromoNo]/[Promo count]![# of Demos]) AS Percentage
FROM [Promo count] INNER JOIN [Promo Executed] ON [Promo count].PromoNo = [Promo Executed].PromoNo
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos], [Promo Executed].CountOfPromoNo;

Try executing this and observe PCPN and PEPN

 

[boblarson]

Just a suggestion, but you should avoid using special characters in field names (example: #). This one is used with Text inputs and outputs.

Use of special characters or reserved words in field names can unexpectedly cause you great pain and suffering.

 

[GATJA]

Hmmm, that didn't seem to work. This may be a really stupid question, but what is PCPN and PEPN? I may have forgotten it or just never learned anything about it.

 

[neileg]

Hmmm, that didn't seem to work. This may be a really stupid question, but what is PCPN and PEPN? I may have forgotten it or just never learned anything about it.

Erm, how about an abbreviation?
Promo Count Promo No and Promo Executed Promo No

 

[GATJA]

Heh, I just got that right before I checked back on here. See, I told you it was a stupid question, ha.

 

[GATJA]

I fixed the doubles at one point in time, but when I renamed all my queries that feed into this one, I gained doubles again. I don't know how, anyone? Here is my SQL:

SELECT [Promo Booked BMS].PromoNo, [Promo Booked BMS].[From BMS], Sum([Promo Executed DMS].[From DMS]) AS [SumOfFrom DMS], IIf([Promo Executed DMS]![From DMS] Is Null,0,[Promo Executed DMS]![From DMS]/[Promo Booked BMS]![From BMS]) AS Percentage
FROM [Promo Booked BMS] LEFT JOIN [Promo Executed DMS] ON [Promo Booked BMS].PromoNo = [Promo Executed DMS].PromoNo
GROUP BY [Promo Booked BMS].PromoNo, [Promo Booked BMS].[From BMS], IIf([Promo Executed DMS]![From DMS] Is Null,0,[Promo Executed DMS]![From DMS]/[Promo Booked BMS]![From BMS]);

I have a feeling something in this query went wrong (Promo Executed DMS):

SELECT [Promo Booked BMS].PromoNo, Count(DemoOrder.PromoNo) AS [From DMS]
FROM DemoOrder RIGHT JOIN [Promo Booked BMS] ON DemoOrder.PromoNo = [Promo Booked BMS].PromoNo
GROUP BY [Promo Booked BMS].PromoNo, DemoOrder.Status, DemoOrder.DemoDate
HAVING (((DemoOrder.Status)="E") AND ((DemoOrder.DemoDate)>=#12/15/2005#));

This seecond query I think is where the doubles are coming from