Negative Figures In A Query

  • Thread starter Thread starter Bateman28
  • Start date Start date
B

Bateman28

Hi

I have greated a query that sums up alot of different fields into numbers,
the only problem is the numbers always return as a negative! How do I need to
wright the expressions so that the figures return a positive number and not a
negative.

Example: I get (-214) when it should be (214)

Any help would be great
 
I have tried both types and i get the same error.

Error circular reference caused by alias " Field Name " in Query Definitions
select list.

The query has selected files that i can't change as they pick up specific
files in a specific format that i need.....

anymore help would be appreciated
 
Post the SQL View of your query.

Try changing the alias of "Field Name" in the query.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I have tried both types and i get the same error.
Click to expand...

Correct the error in your query. If you would like help doing so please post
the SQL view of the query - we can't see it from here.
Error circular reference caused by alias " Field Name " in Query Definitions
select list.
Click to expand...

If you're trying to define [Field Name] on the basis of [Field Name] you'll
get this error; e.g.

[Field Name]: Abs([Field Name])

can't work. Try

[Corrected field name]: Abs([Field name])

or some *other* name.
The query has selected files that i can't change as they pick up specific
files in a specific format that i need.....
Click to expand...

You'll need to explain that statement. Do you mean "fields" instead of
"files"? What does the format have to do with anything?
 
As you can see there is alot of script in my query:

SELECT DISTINCTROW [Branch Codes].MarketArea, IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]) AS Branch, Year([SaveDate]) AS [Year],
Format([SaveDate],'mmmm') AS [Month], Month([SaveDate]) AS MonthNum,
[NonOpps]+[Opps] AS Calls, Sum(tbl_MAIN.NonOpps) AS NonOpps,
Sum(tbl_MAIN.Opps) AS Opps, Sum(tbl_MAIN.BookingMade) AS SumOfBookingMade,
Sum(tbl_MAIN.NonBookings) AS SumOfNonBookings, Sum(tbl_MAIN.PriceMatch) AS
SumOfPriceMatch, Sum(tbl_MAIN.Service) AS SumOfService,
Sum(tbl_MAIN.ServiceMot) AS SumOfServiceMot, Sum(tbl_MAIN.ServiceRepair) AS
SumOfServiceRepair, Sum(tbl_MAIN.MOT) AS SumOfMOT, Sum(tbl_MAIN.MOTRepair) AS
SumOfMOTRepair, Sum(tbl_MAIN.MB2U) AS SumOfMB2U, Sum(tbl_MAIN.Repair) AS
SumOfRepair, Sum(tbl_MAIN.RepeatRepair) AS SumOfRepeatRepair,
Sum(tbl_MAIN.VHC) AS SumOfVHC, Sum(tbl_MAIN.VHCRepair) AS SumOfVHCRepair,
Sum(tbl_MAIN.WarrantyRepair) AS SumOfWarrantyRepair, Sum(tbl_MAIN.NotDueYet)
AS SumOfNotDueYet, Sum(tbl_MAIN.CustomerAvailabiity) AS
SumOfCustomerAvailabiity, Sum(tbl_MAIN.Price) AS SumOfPrice,
Sum(tbl_MAIN.NoLongerHaveTheVehicle) AS SumOfNoLongerHaveTheVehicle,
Sum(tbl_MAIN.SellingVehicle) AS SumOfSellingVehicle, Sum(tbl_MAIN.Escalation)
AS SumOfEscalation, Sum(tbl_MAIN.ServiceQuotation) AS SumOfServiceQuotation,
Sum(tbl_MAIN.ServiceMotQuotation) AS SumOfServiceMotQuotation,
Sum(tbl_MAIN.MOTQuotation) AS SumOfMOTQuotation,
Sum(tbl_MAIN.BookedAtOtherMBRSite) AS SumOfBookedAtOtherMBRSite,
Sum(tbl_MAIN.CollectionAndDeliveryArranged) AS
SumOfCollectionAndDeliveryArranged, Sum(tbl_MAIN.In48Hrs) AS SumOfIn48Hrs,
Sum(tbl_MAIN.Amendment) AS SumOfAmendment, Sum(tbl_MAIN.Cancellation) AS
SumOfCancellation, Sum(tbl_MAIN.ServiceInformation) AS
SumOfServiceInformation, Sum(tbl_MAIN.TransferredInErrorNew) AS
SumOfTransferredInErrorNew, Sum(IIf([TransferredInErrorReason]="STM
Enquiry",1,0)) AS [TIE STM Enquiry],
Sum(IIf([TransferredInErrorReason]="Breakdown",1,0)) AS [TIE Breakdown],
Sum(IIf([TransferredInErrorReason]="Progress Update - Vehicle On Site
Today",1,0)) AS [TIE Update], Sum(IIf([TransferredInErrorReason]="Parts
Enquiry",1,0)) AS [TIE Parts Enquiry],
Sum(IIf([TransferredInErrorReason]="Directions",1,0)) AS [TIE Directions],
Sum(IIf([TransferredInErrorReason]="Technical Enquiry",1,0)) AS [TIE
Technical], Sum(IIf([TransferredInErrorReason]="Walk In / Same Day
Request",1,0)) AS [TIE Walkin], Sum(IIf([TransferredInErrorReason]="Body
Services / Accident Repair
Enquiry",1,0))+Sum(IIf([TransferredInErrorReason]="Dealership
Information",1,0))+Sum(IIf([TransferredInErrorReason]="Sales",1,0))+Sum(IIf([TransferredInErrorReason]="Service
History Enquiry",1,0))+Sum(IIf([TransferredInErrorReason]="Tyre
Enquiry",1,0))+Sum(IIf([TransferredInErrorReason]="Windscreen Enquiry",1,0))
AS [TIE Misc], Sum(tbl_MAIN.MBRUCBooking) AS SumOfMBRUCBooking
FROM [Branch Codes] RIGHT JOIN tbl_MAIN ON [Branch Codes].BranchName =
tbl_MAIN.Retailer
GROUP BY [Branch Codes].MarketArea, IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]), Year([SaveDate]),
Format([SaveDate],'mmmm'), Month([SaveDate])
HAVING (((IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]))>""))
ORDER BY IIf([tbl_main].[retailer] Like
"*MBD*","MBD",[tbl_main].[retailer]), Month([SaveDate]);


Hope This Helps John!
 
Back
Top