Count down please HELP

[Jaharul]

Hi All

I want to subtract a rolling form which is updated daily against a budgeted figure but i jus cant get it to work please help. Its basically like a stock system when an item is sold the quantity in stock is reduced.

Many thanks to the helper.

 

[Jaharul]

i dont realy know how to do that

 

[bat17]

OK, in the design view of the query, where you have the grid,
go View>SQL View and you will get the text that makes up your query. Copy this and just paste it.

Peter

 

[Jaharul]

SELECT [Rolling Travel 2004].Destination, [Travel Matrix].[# of Trips], [Travel Matrix].[# of People on Trip(s)], [Travel Matrix].[# Total trips], [Travel Matrix].F10, Count([Rolling Travel 2004].[Date of Dep]) AS [CountOfDate of Dep]
FROM [Rolling Travel 2004] LEFT JOIN [Travel Matrix] ON [Rolling Travel 2004].Destination = [Travel Matrix].F10
GROUP BY [Rolling Travel 2004].Destination, [Travel Matrix].[# of Trips], [Travel Matrix].[# of People on Trip(s)], [Travel Matrix].[# Total trips], [Travel Matrix].F10;

Thats the SQL view

 

[bat17]

Which field do you want to subtract from the other?

peter

 

[Jaharul]

i want to subtract the count of dep date against the # total trips

 

[bat17]

try this and see if it does what you want

SELECT [Rolling Travel 2004].Destination, [Travel Matrix].[# of Trips], [Travel Matrix].[# of People on Trip(s)], [Travel Matrix].[# Total trips], [Travel Matrix].F10, Count([Rolling Travel 2004].[Date of Dep]) AS [CountOfDate of Dep], [# Total trips]-[CountOfDate of Dep] AS Balance
FROM [Rolling Travel 2004] LEFT JOIN [Travel Matrix] ON [Rolling Travel 2004].Destination = [Travel Matrix].F10
GROUP BY [Rolling Travel 2004].Destination, [Travel Matrix].[# of Trips], [Travel Matrix].[# of People on Trip(s)], [Travel Matrix].[# Total trips], [Travel Matrix].F10;


Peter

 

[Jaharul]

Thanks alot mate it seems to be workin but now i have a problem with the count of dep date it seems to be counting the figure wrong

 

[bat17]

The value of count will be the number of records in each group as defined by your grouped field. Do you actualy need all of the fields that you are grouping on in there? In what way is the count wrong?

Peter

 

[Jaharul]

the count is fine but it seems that a country like ukraine for example showing up about 5 different times which in some places causes the count to be over stated.

 

[bat17]

the query looks ok. I would check your data, are either of the two tables in the query actualy other queris? if so double check the data in them to make sure they are not doubling up somehow.

Peter