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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|