Query from more than one table

[Bryan]

Here's my dilemna. I have 206 apartments. Their lease expiration dates are
scattered across the calendar. I have three things to compare. 1. The
actual count of leases expiring in each month. I did that in a query ok,
bringing me to the same 12 items as the target and max. 2. A lease
expiration target and 3. A lease expiration Maximum. These last 2 are
always going to be a list 12 long, which is why the actual needs to be 12
long. What has been requested is to have these all in a form so they have a
visual and then I will color the MMM text in green up until they approach
Target-2, then turn orange until they reach max-2, then turn red. I can
handle the colors all right if I can figure out how to get them into a single
query so I can present them in a single form. I have tried to set up a
relationship between the Month field, but when I do, nothing shows up in my
datasheet view in my query.
My tables are:
Table1
Apartment Number
Lease Expiration date
Likely to renew?
Lease term
Month (Based on exp dt, MMM)

Table2
Month (Jan - Dec)
Actual (Calculated from lease exp dt)
Target (Set entered values)
Max (Set entered values)

Thoughts??

 

[SteveS]

Here's my dilemna. I have 206 apartments. Their lease expiration dates are
scattered across the calendar. I have three things to compare. 1. The
actual count of leases expiring in each month. I did that in a query ok,
bringing me to the same 12 items as the target and max. 2. A lease
expiration target and 3. A lease expiration Maximum. These last 2 are
always going to be a list 12 long, which is why the actual needs to be 12
long. What has been requested is to have these all in a form so they have a
visual and then I will color the MMM text in green up until they approach
Target-2, then turn orange until they reach max-2, then turn red. I can
handle the colors all right if I can figure out how to get them into a single
query so I can present them in a single form. I have tried to set up a
relationship between the Month field, but when I do, nothing shows up in my
datasheet view in my query.
My tables are:
Table1
Apartment Number
Lease Expiration date
Likely to renew?
Lease term
Month (Based on exp dt, MMM)

Table2
Month (Jan - Dec)
Actual (Calculated from lease exp dt)
Target (Set entered values)
Max (Set entered values)

Thoughts??

Hi Bryan,

I've been thinking about your problem and I don't see any relationship between
your tables.

I have some questions:

about Table1:
Apartment Number: - most likely text
Lease Expiration date: - Date -
why are you storing the Expiration date? I would think you would store

the start date and calc the Expiration date since you have the lease
term.
Month: text(?) If it's Based on exp dt, you can calculate this.

about Table2:
How is table2 related to table1- if at all?
Are the field entries *Per Year* or does it matter which year?

Month: -text - the names of the months
Actual: - Date(?) If it is Calculated from lease exp dt, then you shouldn't store it.
Target (Set entered values): -Integer(?) - The num of leases exp per month?
Max (Set entered values): -Integer(?) - Max num of leases exp/month?

If you change the target and max, then you need a date of change field or the
year the target and max are for.


Are you trying to make a form that looks like:

Actual Projected Target Max
Jan 5 3 6
Feb 7 7 6
Mar 2 5 6
Apr 4 5 6
May 8 5 6
Jun 6 5 6

 

[Bryan]

Steve,
Thanks for the response. I figured out that I needed to create another
table with fields from the first two and set relationships between them. I
then created my query and that all worked great.... with one problem. I
can't set up cascading updates, because I am using a date field which can
have nulls and will have duplicates to link the tables, hence no unique
index. My subform is as you pictured it; exactly my thought. I have
discovered color coding will not work without coloring all the months at
once, so I have abandoned that. My tables are as follows:

Table1 Table3 Table2
LeaseExpDt---leaseExpDt
.. Month-----------------Month
.. CountofLeaseExpDt Target
.. Max

The query then is:
Date(Table1), Month(Table3), CountofLeaseExpDt(Table2),Target(Table2),
Max(Table2)

I'll be really happy if I can get table3 to update from table1!!!!! Not
really sure how to do it though.
I appreciate your input.

 

[SteveS]

Steve,
Thanks for the response. I figured out that I needed to create another
table with fields from the first two and set relationships between them. I
then created my query and that all worked great.... with one problem. I
can't set up cascading updates, because I am using a date field which can
have nulls and will have duplicates to link the tables, hence no unique
index. My subform is as you pictured it; exactly my thought. I have
discovered color coding will not work without coloring all the months at
once, so I have abandoned that. My tables are as follows:

Table1 Table3 Table2
LeaseExpDt---leaseExpDt
. Month-----------------Month
. CountofLeaseExpDt Target
. Max

The query then is:
Date(Table1), Month(Table3), CountofLeaseExpDt(Table2),Target(Table2),
Max(Table2)

I'll be really happy if I can get table3 to update from table1!!!!! Not
really sure how to do it though.
I appreciate your input.

Bryan,

I *think* I found a solution for you, without using Table3. But you can't
update records, just view them. If you want a printout, set up a report using
qryExpirations as the report record source.

**1) I created two tables:

Name: Table1

ID: Long Integer
AptNum: Text
LeaseExpDt: Date/Time


Name: Table2

ID: Long Integer
dteMonth: Text
Target: Long Integer
MX: Long Integer


**2) then I created 3 queries

Name: qryActual

In SQL view, paste this

SELECT Month([LeaseExpDt]) AS MthNum, Format([LeaseExpDt],"mmm") AS Mth,
Count(Month([LeaseExpDt])) AS Actual
FROM Table1
WHERE (((Month([LeaseExpDt]))<Month(Date())) AND
((Year([LeaseExpDt]))=Year(Date())))
GROUP BY Month([LeaseExpDt]), Format([LeaseExpDt],"mmm")
ORDER BY Month([LeaseExpDt]);


Name: qryProjected

In SQL view, paste this

SELECT Month([LeaseExpDt]) AS MthNum, Format([LeaseExpDt],"mmm") AS Mth,
Count(Month([LeaseExpDt])) AS Projected
FROM Table1
WHERE (((Month([LeaseExpDt]))>=Month(Date())) AND
((Year([LeaseExpDt]))=Year(Date())))
GROUP BY Month([LeaseExpDt]), Format([LeaseExpDt],"mmm")
ORDER BY Month([LeaseExpDt]);


Name: qryExpirations

In SQL view, paste this
SELECT Table2.dteMonth, qryProjected.Projected, qryActual.Actual,
Table2.Target, Table2.MX
FROM (Table2 LEFT JOIN qryProjected ON Table2.dteMonth = qryProjected.Mth)
LEFT JOIN qryActual ON Table2.dteMonth = qryActual.Mth;



**3) Create a form

Record Source: qryExpirations
Default View: Continuous Forms

Add the 5 fields to the Detail section
put the labels in the Form Header
Save the form and view it.
++++++++++++++++++++++++++++++++++++++


I think you are causing problems for yourself by having fields in the tables
that can be calculated. (See my questions in my previous post.)

Anyway, try it and see if it does what you want.


Steve