"PC Datesheet" <no****@nospam. spam> wrote in message news:<By******* **********@news read2.news.atl. earthlink.net>. ..
You would only need to post the pertinent tables and only the pertinent fields
in the tables. Usually it is done here like this:
TblMytableA
MyTableAID
FieldA1
FieldA2
etc
TblMyTableB
MyTableGID
FieldB1
FieldB2
etc
Steve
PC Datasheet
"John Smith" <jo********@hot mail.com> wrote in message
news:40******** **************@ news.newsgroups .ws... Thanks,
How do I do this?
John
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Hi Steve,
Each query is made as follows
tbl Accommodation
tbl Interests
tbl Total Accommodation Sq Ft
They are linked as follows:
tbl Accommdation > tbl Interests One to Many Relationship through a
Property ID ((Site No).
tbl Accommodation > tbl Accommodation Sq Ft One to One. The purpose
of the latter table is simply to provide a total sq ft for each
property, the property ID (site no) being the link.
The query is then constructed as follows:
Site No (tbl Accommodation)- a unique reference for the property.
Property Name (tbl Accommodation)- what the property is called.
Sq Ft (tbl Accommodation) - how big is the area. There can be more
than one record per site no but the aggregate of the records cannot
exceed the total for the whole building.
Vacancy Date (tbl Accommodation) - when did it become vacant.
Area % (This is a an expression calculating the % of accommodation for
the record as against the whole building.
Month. This is a date ie, 1/1/04 and the idea is to create a separate
query for each month whereby this month would change consecutively eg,
1/2/04, 1/3/04 etc
Next Review (tbl Interests) What is the date upon which the rent is
reviewed.
RRD
VD
DBED
These three cells return 0, -1 (true, false) dependent upon certain
dates that impact whether a charge is made.
Rent (tbl Interests) This is the total rent for the Property
Expression 1 This is the key to the query. It calculates whether a
charge is relevant to that month in question. I have copied the
expression; you won't understand it all but it will give you an
indication of what it's trying to achieve
Expr1: IIf([DBED]=0 Or [VD]=0 Or [Area %] Is Null,0,IIf([RRD]=-1 And
[DBED]=-1 And [VD]=-1 Or [Estimated Rent Review] Is Null,[Rent]*[Area
%]/12,IIf([RRD]=0 And [DBED]=-1 And [VD]=-1 Or [VD] Is Null,[Estimated
Rent Review]*[Area %]/12,[Rent]*[Area %]/12)))
Estimated Rent Review (tbl Interests) If the rent increases during
the period, to what value.
End Date - This is an expression that calculates which of the earlier
of three dates does the agreement end on.
Disposal Date (tbl Interests)
Break Dates (tbl Interests)
Lease End Date (tbl Interests)
These are the three dates that feed into the End Date.
If you follow all of this then I take my hat off to you. I am 99%
happy that this query works. I have tested various scenarios using
different variables and Expr 1 appears to return the right monthly
value for the various records in the database.
The problem appears when I try and combine this query in a query that
draws together identical queries for each month, ie Jan, Feb, Mar, Apr
etc. The queries are exactly the same with the exception of the Month
where the date changes. I was hoping that the query would combine in
order that I could consolidate each monthly charge in a single
location ie,
Expr1 Jan
Expr1 Feb
Expr1 Mar
Expr1 Apr and so on.
However it is here that I have had problems with getting the right
join. It would seem that I cannot create a one to many relationship
of enforce referential integrity and a cartesian product join.
Hope this all makes sense and thanks for your help.
John
