A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Parameter variables



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 09:59 AM posted to microsoft.public.access
Jim L.[_2_]
external usenet poster
 
Posts: 56
Default Parameter variables

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
  #2  
Old May 28th, 2010, 02:49 PM posted to microsoft.public.access
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Parameter variables

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

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


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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.