sir_dan_mitchell
Registered User.
- Local time
- Today, 17:26
- Joined
- Feb 5, 2002
- Messages
- 74
Hi guys,
Still havent managed to clear this problem up, so im guna place all the detail I can here, hopefully one of you will have an idea.
Basically I have a 2 tables...Participants and Socials.
I want to have Participants being added to their socials and basically I have worked everythign out fine its just a calculation in the query that I simply cannot understand.
The important fields I have in the Participants table are [Member?] and [Date Joined].
The important field in in the Socials table is [Social Date]
Since there are different prices for a social depending on whether they are a member or not the query has to consist of the following.
If they are a member, then charge [Members Price], if not [Non [members price] the only addition and previous problem is that a Participant can become a member later and this would date back and change all previous figures.
I found the easiest way around this was to create 3 queries.
Members query - which would add up all the money for those people who are members
Non members query - simply with a No for [Member?]
Non members query & Extra - which is [Member?] Yes, but [Date Joined] is > = [Social Date].
I had problems with Nulls too as they had to give 0's instead otherwise they wouldnt add up.
ALL THESE QUERIES WORK FINE!!!
The problem seems to arise when I try to add the queries into a Totals query that I can base my form on. IF the [date joined] is > = [Social date] then there is a DATA TYPE MISSMATCH error when I use Sum( the 3 queries). The strange thing is that there isnt a missmatch IF the [date joined] is NOT > = [Social date]
Anyone any ideas? PLEEEEASE I have been working on this for a while and I dont understand why these values cant simply sum together!??
Still havent managed to clear this problem up, so im guna place all the detail I can here, hopefully one of you will have an idea.
Basically I have a 2 tables...Participants and Socials.
I want to have Participants being added to their socials and basically I have worked everythign out fine its just a calculation in the query that I simply cannot understand.
The important fields I have in the Participants table are [Member?] and [Date Joined].
The important field in in the Socials table is [Social Date]
Since there are different prices for a social depending on whether they are a member or not the query has to consist of the following.
If they are a member, then charge [Members Price], if not [Non [members price] the only addition and previous problem is that a Participant can become a member later and this would date back and change all previous figures.
I found the easiest way around this was to create 3 queries.
Members query - which would add up all the money for those people who are members
Non members query - simply with a No for [Member?]
Non members query & Extra - which is [Member?] Yes, but [Date Joined] is > = [Social Date].
I had problems with Nulls too as they had to give 0's instead otherwise they wouldnt add up.
ALL THESE QUERIES WORK FINE!!!
The problem seems to arise when I try to add the queries into a Totals query that I can base my form on. IF the [date joined] is > = [Social date] then there is a DATA TYPE MISSMATCH error when I use Sum( the 3 queries). The strange thing is that there isnt a missmatch IF the [date joined] is NOT > = [Social date]
Anyone any ideas? PLEEEEASE I have been working on this for a while and I dont understand why these values cant simply sum together!??
