Show only current and future information in an update form??

[kathryn]

OK this one has me stumped. I am sure that it is simple, but I can't see it. I have a table with data similar to:


ID BranchCode BranchAddress AddressEffDate
--- ---------- ------------- --------------
1 AL001 123 Main Street 9/1/00
2 AL001 234 Elm Street 10/1/00
3 AL001 345 Oak Street 12/15/00
4 AL002 456 Fletcher 1/1/00
5 AL003 567 Fowler 2/1/00

I need the update form for this data to show the current data AND any future data. If I don't show the future data, the woman doing data entry will just keep entering it over and over. The data must be updatable, which is what is tripping me up.

I am thinking, (me the queen of database design!), that this should be broken up into two tables, but I REALLY don't have time to do this. (I know, I know, it's better to do it right, but I am swamped.)

Anyone have an answer?????



Kathryn

 

[MichaelRed]

'qryMaxBrDate
SELECT tblBranchAddr.BranchCode, tblBranchAddr.AddressEffDate
FROM tblBranchAddr
GROUP BY tblBranchAddr.BranchCode, tblBranchAddr.AddressEffDate
HAVING (((tblBranchAddr.AddressEffDate)>=Now()));


'qryUniqueBr
SELECT DISTINCTROW tblBranchAddr.BranchCode, tblBranchAddr.AddressEffDate
FROM tblBranchAddr
WHERE (((tblBranchAddr.AddressEffDate)<=Now()));


'qryCurrDate
SELECT qryUniqueBr.BranchCode, Max(qryUniqueBr.AddressEffDate) AS MaxOfAddressEffDate
FROM qryUniqueBr
GROUP BY qryUniqueBr.BranchCode;

qryAllBrDates
Select * from qryMaxBrDate
Union Select * from qryCurrDate;

'qryFormRecordSource
SELECT tblBranchAddr.*
FROM tblBranchAddr INNER JOIN qryAllBrDates ON (tblBranchAddr.AddressEffDate = qryAllBrDates.AddressEffDate) AND (tblBranchAddr.BranchCode = qryAllBrDates.BranchCode);


kathryn,

Hopefully this crude exercise will inspire your queenly instincts. It works for the test data supplied, and I added one additional (future) record just to &quot;prove&quot; that ALL future address changes are reflected.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Donkeygirl]

well, michael's will probably work better, but if they are one of a kind, just index the fields and don't allow updates if duplicates are being made (ie use a message box to tell data entry that record is already there)
I have a favor to ask. I posted a question on forms, stupidly in the general discussion, but desperately need some help. I read your bio and saw the intelligence. Would you mind looking up my question to see if you can help? It is titled lookup form. If you can I will be eternally greatful...