Creating a query

[Fie]

hi,

Right am having a wee bit of trouble in trying to finsh of a query that
i have created.

Basically every week 50 school's have figures for No tickets, Tickets
sold, Tickets Returned... based on these 3 figures for 50 schools over
52 weeks i have done a calculation to find the ticket differnece each
week for each individual school which i have used

Ticket Diff: [No Tickets]-[Tickets Ret] thus giving me answer i need.
This is were i now am lost, what i then need to do is calculate a total
ticket difference for each school for each month...

eg. Findochty Primary ticket differences for april are
week 1 --- 12
Week 2 --- 15
Week 3 --- 14
Week 4 --- 14

i need to total this and the multiply by £1.70 (thats the ticket
price) to get total loss on tickets for the month.

how do i do this...???

 

[Jeff Boyce]

Fie

Do you have "month" and "week" recorded, or do you have a "date" recorded?

It's more difficult to convert month and week to Access-usable dates (for
Access date-related functions) than it is to use an actual date in the
functions.

For example, if you have a date recorded in each row in the table you are
using to hold this data, you could create a query that sums by month (and
year -- don't forget, April happens every year!).

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

hi,

Right am having a wee bit of trouble in trying to finsh of a query that
i have created.

Basically every week 50 school's have figures for No tickets, Tickets
sold, Tickets Returned... based on these 3 figures for 50 schools over
52 weeks i have done a calculation to find the ticket differnece each
week for each individual school which i have used

Ticket Diff: [No Tickets]-[Tickets Ret] thus giving me answer i need.
This is were i now am lost, what i then need to do is calculate a total
ticket difference for each school for each month...

eg. Findochty Primary ticket differences for april are
week 1 --- 12
Week 2 --- 15
Week 3 --- 14
Week 4 --- 14

i need to total this and the multiply by £1.70 (thats the ticket
price) to get total loss on tickets for the month.

how do i do this...???

 

[Fie]

I have a date recored...!!Right av changed them to month and year...
how do i perform the query, what criteria questions do i need to ask to
perfrom the
query???

 

[Jeff Boyce]

Fie

Check Access HELP for date-related functions. You can create "new" fields
in a query by typing something like:

Month([YourDateField])
and
Year([YourDateField])

then adding a selection criterion underneath each that prompts you for input
(see "parameter query" in Access HELP), looking something like:

[For which month?]
and
[For which year?]

Note that Month() returns numbers 1-12, not "text".

Regards

Jeff Boyce
<Office/Access MVP>