Parameter variables

[Jim L.]

Part of the DB I am working on contains a table for Emergency Response
data. The primary key ([ERptNo]) is formatted as "yymmddhhmm_ _ _", the last
3 digits being the first 3 letters of the responders last name. If they were
the Incident Commander, additional information is collected & added to the IC
table, with a primary key ([ERNo]) of "yymmddhhmm". I've built a query with
fields from these tables, and need to find a way to add parameters for a
search which would open a report. I would like the IC report to show up as
the first page, then all additional responders reports on the following pages
of the report.
Some of the responder reports may be up to 30 minutes earlier or later than
the IC report time, so the primary key numbers may not match others from the
same incident, not to mention the letters from the last names.
How can I write the query parameter in the [ERptNo] field to search for the
IC primary key [ERNo] (year, month, date, hour & minute) plus or minus 30
minutes to include all responder reports, and then a wildcard to bring up all
of the different last name letters? I'm just a beginner in writing code, so
if this is possible, a detailed explanation would be appreciated.
Thank you

 

[Tom van Stiphout]

On Fri, 28 May 2010 01:59:01 -0700, Jim L.

You need to start at the beginning, which is the database design. This
PK is HORRIBLE and will continue to cause much grief. Scrap it. Do not
use ANY fields with concatenated data in your new design. Also do not
format ANY fields - that is left for the forms and reports that use
the data.
I don't know enough about your exact requirements to offer a complete
database design, but hopefully this will get the idea across:
tblIncidents
IncidentID autonumber, PK
IncidentDate datetime, required
IncidentLocation [unsure of data type]
ReportedBy [usure of data type]

tblResponders
IncidentID long int, required, FK, PK
EmployeeID long int, required, FK, PK
RoleID long int, required, FK [from tblJobPositions?]
ResponseDate datetime, required

tblEmployees
EmployeeID autonumber, PK
FirstName text20 required
LastName text30 required
JobPositionID long int, required, FK

Once you have a normalized database design everything else will fall
into place much more easily. Why? Because Access is built as a
relational database package, and its features expect and work best
with a good design.

-Tom.
Microsoft Access MVP