B
Barry Claxton
I look after the admin for our fishing club. I've created a database (access
2003) with around 1200 records. we have members and non members who fish with
seperate tables for each. Ive been working on a method to show the takings
from between two dates for the non members. I've achieved it with one date
(eg todays, or yesterdays) but if I put dates in covering several days or
weeks It serves up junk! The problem is to create a query which will show
each date on which someone fished (between the criteria dates entered using a
form into the "date of fishing" field in the query) but not show that the
licence and admin fee was paid on EACH of those dates as it currently is
doing.
For example, if someone fished on 5 occasions (say) during the last 2 weeks,
my report, based on this query, says he paid his licence and admin 5 times
too, which doesn't make for good acounting!
The query is based on two tables. "Non club anglers", which contains
personal details of the individual and includes the dates of paying for
fishing licence and administration fee (yearly) plus two further fields for
the amounts in question.
The second table is a record of dates fished or fishing history ("date of
fishing") and other small details (where fishing, how many in party etc)
The two tables are linked through the "personal ID num" fields in each
table. I've experimented with various combinations of link but the problems
the same whichever I try. In essence if I was instructing the query in plain
english I'd say "show me, between the dates I enter, how many times each
individual fished, and whether, during that period he happened to pay for his
licence to be renewed (and therefore, his administration fee) [both anual
events)
Im currently learning VBA but am not good enought yet to create a program,
but can't help but feel that has to be the way to do it because somehow I
need to say "if this guys paid his licence, only show it once, not for each
time hes fished!
2003) with around 1200 records. we have members and non members who fish with
seperate tables for each. Ive been working on a method to show the takings
from between two dates for the non members. I've achieved it with one date
(eg todays, or yesterdays) but if I put dates in covering several days or
weeks It serves up junk! The problem is to create a query which will show
each date on which someone fished (between the criteria dates entered using a
form into the "date of fishing" field in the query) but not show that the
licence and admin fee was paid on EACH of those dates as it currently is
doing.
For example, if someone fished on 5 occasions (say) during the last 2 weeks,
my report, based on this query, says he paid his licence and admin 5 times
too, which doesn't make for good acounting!
The query is based on two tables. "Non club anglers", which contains
personal details of the individual and includes the dates of paying for
fishing licence and administration fee (yearly) plus two further fields for
the amounts in question.
The second table is a record of dates fished or fishing history ("date of
fishing") and other small details (where fishing, how many in party etc)
The two tables are linked through the "personal ID num" fields in each
table. I've experimented with various combinations of link but the problems
the same whichever I try. In essence if I was instructing the query in plain
english I'd say "show me, between the dates I enter, how many times each
individual fished, and whether, during that period he happened to pay for his
licence to be renewed (and therefore, his administration fee) [both anual
events)
Im currently learning VBA but am not good enought yet to create a program,
but can't help but feel that has to be the way to do it because somehow I
need to say "if this guys paid his licence, only show it once, not for each
time hes fished!