Using a table to hold the "start" and "end" dates of various "ranges of
interest" is an ok thing to do. I'd use a table structure similar to this:
tPeriodsOfInterest
PeriodID
PeriodStartDate
PeriodEndDate
What I would do is use a form that has a combo box and a command button on
it. The combo box should use a query as its Row Source, and the query should
return the records from the tPeriodsOfInterest table.
SELECT * FROM tPeriodsOfInterest ORDER BY PeriodStartDate DESC;
For the combo box's properties, I'd set the following:
Bound Column 1
Column Count 3
Column Widths 0";.75";.75"
Column Headings Yes
The user can select the desired range, and then click the command button to
run a report. This would use the DoCmd.OpenReport action in the Click
event's code.
The report should use a query as its RecordSource. This query should use the
combo box to decide how to filter itself:
SELECT * FROM tFamilyJournal
WHERE tFamilyJournal.[date] BETWEEN
DLookup("perStart", "tPeriodsOfInterest",
"PeriodID=" & Forms!MyFormName!ComboBoxName)
AND DLookup("perEnd", "tPeriodsOfInterest",
"PeriodID=" & Forms!MyFormName!ComboBoxName);
This allows each person to look at data simultaneously. Also note: your
users should not all be using the same database file at the same time. You
should split the data into a backend (which is then placed on a server that
all users can access) and use a frontend file to access the data. And each
user gets his/her own copy of that frontend database. See Tony Toews'
website for info about this:
http://www.granite.ab.ca/access/splitapp/index.htm
Also note that you should not use Date as the name of a field. Date is a
reserved word in ACCESS; using it as a field or control name can greatly
confuse ACCESS and cause cause your table, form, or report to stop working
correctly. See KB article number 286335 for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
--
Ken Snell
<MS ACCESS MVP>
Eric Nelson said:
tFamilyJournal is full of charges and credits for lots of families. I'd like
invoices to show only those charges and credits for the current fiscal year.
To do that I thought to create a date-constrained view of tFamilyJournal. How
best to set that up is the crux of my question.
My own idea is to set up tPeriodsOfInterest with a list of interesting date
ranges (e.g. calendar & fiscal quarters and years) with a boolean field to
switch the range on or off. A variety of date-related views could be created
in the db by joining tPeriodsOfInterest with the original table.
Thus, the query for the date-constrained view of tFamilyJournal looks like:
SELECT tFamilyJournal.*
FROM tPeriodsOfInterest INNER JOIN tFamilyJournal ON
(tFamilyJournal.date>=tPeriodsOfInterest.perStart) AND
(tFamilyJournal.date<=tPeriodsOfInterest.perEnd)
WHERE (tPeriodsOfInterest.perInclude = True);
BTW, this does NOT solve the multiuser problem where users might wish to
look at different periods... any thoughts on that?
All comments gratefully received.
Thanks,
Eric
Ken Snell said:
Not so much "matching" as the ones outside your desired range (which I
assume is represented by the fields tPeriodsOfInterest.perStart and
tPeriodsOfInterest.perEnd) are filtered out by the query, not by an join.
On the fact of it, I'm not sure that I would use this type of query and
setup to achieve your result, but, as I have no idea of what types of
records are in tPeriodsOfInterest table, I cannot offer an alternative way.
If you post more info about your tables and their contents, perhaps I could.
--
Ken Snell
<MS ACCESS MVP>
Eric Nelson said:
You mean "as there are matching records" right? That's what I've observed
anyway. If two time periods in tPeriodsOfInterest overlap the tFamilyJournal
entries within the overlap are duplicated.
But I'm more interested in the larger question... is this a good way to
solve my original problem of constraining the view of the journal to the
current period of interest?
If not, what's the better way?
Thanks,
E
:
Your SQL creates a cartesian query, meaning that each record in
tFamilyJournal table is displayed the same number of times as there are
records in tPeriodsOfInterest.
--
Ken Snell
<MS ACCESS MVP>
OK I'll seed the discussion with this:
SELECT tFamilyJournal.*
FROM tPeriodsOfInterest, tFamilyJournal
where tFamilyJournal.date >= tPeriodsOfInterest.perStart and
tFamilyJournal.date <= tPeriodsOfInterest.perEnd
It works... tPOI can have multiple entries & they get "chosen" seemingly
correctly.
So what IS this SQL statement anyway? Is it an implicit inner JOIN?
Thanks [even more] for [even more] insight
Eric
:
I have a table of customer journal entries (charges,
credits)... I'd like the customer invoice to only show
current fiscal year activity. What's the best way to do
that? One thought I had was to create a current-year view
(query) of the table. Ultimately it would be nice to have
a form somewhere else that determined which period was of
interest... I suppose that info would be saved in a one-
line table, but then how does that restrict the query?
Thanks for any insight.
Eric
