Tracking Correspondence

[Valery]

Hi! Hope someone can assist

I have several reports which are sent to members residing at a specified Unit in a Coop. I would like to track - automatically vs manually (going into each record), which reports (letters...) have been sent (mailed) to unit 1, unit 2, unit 3... with the date sent.

So that when I open the form for Unit 1, I will be able to see like a drop down of all correspondence sent and on what date and by whom...

A sample database would be GREAT!!

Note: I am a basic intermediate user. Therefore, if you are nice enough to provide coding, you will need to be very specific and tell me where and how it goes - THANK YOU!

 

[CJ_London]

not sure if I can help but you will need to be a bit more specific about what you actually have.

where/how is the correspondence currently stored? if you are talking about files in a directory, is there a rigorously enforced file naming convention - and if so what is it? If talking about a table which stores what/when/who - what is it's design?

 

[Valery]

Thank you for answering! Sorry about the ambiguity.

The correspondence consist of reports. They are part of a database that I am currently building. These reports will be printed and mailed, again and again, with some content variation.

For example, the Rent Increase Report.

A form is used to input the report's date and new rent amounts. So this data is stored in a table like this (just an example, not the real field names):

ID Date Unit Type NewRent
1 15-02-2015 1-bed $800
2 15-02-2015 2-bed $926
3 21-01-2016 1-bed $900

And the report is produced according to the data in the most recent record entered.

What I don't have and would like to create is - I guess a table - to know which Units were sent the letters. Obviously, in this example, everyone would get one. But there are other correspondence that only a select number of units would receive.

So need a table that will - automatically - store:

Unit = 1 (unit number)
Correspondence sent = rent increase (report name)
SentDate = 21-01-2016
SentBy = mail
By = John

I would then be able to produce a report, by Unit #, that lists all the correspondence sent.

Sincerely hope this makes sense. Thank you again for the prompt reply.

 

[CJ_London]

so you have a basis for filtering the report

Suggest use that filter as criteria for an append query to be run to populate your 'sent' table.

The append query can then be run in the same routine which generates the reports.

Alternative is to ask how you send the reports? if by mail, do you have a routine for generating labels or envelopes? Or if emailed a routine for emailing them? If so, this could be an alternative 'source' on which to base your append query. Probably better since you have a much clearer definition of 'sent'.

 

[Valery]

Great idea! Will need to play with it. Thank you so much. Another question - unrelated - if you wish I will post another thread... (not sure I got that wording right, lol)

I am trying to make a query return 1 record - there one that has the closest date entered to today's date. My date field name is PayDate.

NOTE: It may not be the latest record entered.

Can you assist? THANK YOU - please be explicit - where to input coding...

 

[CJ_London]

I am trying to make a query return 1 record - there one that has the closest date entered to today's date. My date field name is PayDate.

by closest I presume you mean before today - something like

 SELECT TOP 1 *
 FROM myTable
 WHERE PayDate<=Date()
 ORDER BY PayDate Desc

if you mean closest - the next one then it would be

 SELECT TOP 1 *
 FROM myTable
 WHERE PayDate>=Date()
 ORDER BY PayDate Asc

if you mean closest - either side you need something completely more complex

 

[GinaWhipp]

Just passing by to say to say... Hello (got your eMail)

I see you are getting great help so I'll be on my way now...

 

[Valery]

Hi Gina! Thank you! CJ, call me dumb but I have tried and tried to modify the query... the only time it worked, it returned record number 1 which is not the latest date (closest to today). Here is the query - how do I modify this? The date field in this one is ApprDate.

SELECT tblRentalRates.RatesID, tblRentalRates.ApprDate, tblRentalRates.EffectDate, tblRentalRates.[1Bed], tblRentalRates.[2BedLow], tblRentalRates.[2BedHigh], tblRentalRates.[2BedAdapted], tblRentalRates.[3BedLow], tblRentalRates.[3BedHigh]
FROM tblRentalRates
WHERE (((tblRentalRates.ApprDate)<=Date()));

Can you modify it so I can simply copy/paste? Thanks again.

 

[CJ_London]

SELECT TOP 1 tblRentalRates.RatesID, tblRentalRates.ApprDate, tblRentalRates.EffectDate, tblRentalRates.[1Bed], tblRentalRates.[2BedLow], tblRentalRates.[2BedHigh], tblRentalRates.[2BedAdapted], tblRentalRates.[3BedLow], tblRentalRates.[3BedHigh]
FROM tblRentalRates
WHERE (((tblRentalRates.ApprDate)<=Date()))
ORDER BY tblRentalRates.ApprDate;

 

[Valery]

Hi CJ - again thank you. I copy/pasted the codes. But it still returns record #1. Here is what is in the table.

RecordID = 1
ApprDate = 03-Aug-14
1Bed = $700

RecordID = 2
ApprDate = 05-Nov-16
1Bed = $900

etc.

I need the record with the last date entered - the closest one to today's date. In this case (there are more records than in this example), record #2.

The coding I copied returns record #1. Did I do something wrong? I opened SQL view and pasted the codes.

Thank you for trying so hard.

 

[CJ_London]

try

ORDER BY tblRentalRates.ApprDate DESC;

 

[Valery]

Bingo! It works! TYTYTYTY