Changing SQL to include a second building

E

E.Q.

We have a database used by maintenance personnel to record equipment issues
discovered during a predictive maintenance round. The techs enter info into
a form that has a subform; the source data for the main form comes from:
SELECT tblRoutLog.*, tblRoutes.chrBuildingNum
FROM tblRoutes INNER JOIN tblRoutLog ON tblRoutes.chrRouteID =
tblRoutLog.chrRout;
The subform (master field: idsRoute, Child Field chrRoute) has a combo box
whose row source is:
SELECT tblEquipment.[Equipment Number], tblEquipment.[Equipment Name]
FROM tblEquipment
WHERE (((InStr([tblEquipment]![Equipment
Number],[Forms]![frmRoutLog]![chrBuildingNum] & '-'))<>'0'));
This works fine for all routes contained in one building. Howerever,
they've added a route covering equipment from two buildings. Is there a way
to amend the SQL in either the main form or subform such that if the route
covers two buildings (in this case, chrRoute = "PM6110", but they could end
up adding others) such that two buildings are selected. (Currently the cbo
picks equipment only in bld 19 when the route covers both building 19 and
building 20.)
Thanks
EQC
 
M

Marco Pagliero

We have a database used by maintenance personnel to record equipment issues
discovered during a predictive maintenance round. The techs enter info into
a form that has a subform; the source data for the main form comes from:
SELECT tblRoutLog.*, tblRoutes.chrBuildingNum
FROM tblRoutes INNER JOIN tblRoutLog ON tblRoutes.chrRouteID =
tblRoutLog.chrRout;
The subform (master field: idsRoute, Child Field chrRoute) has a combo box
whose row source is:
SELECT tblEquipment.[Equipment Number], tblEquipment.[Equipment Name]
FROM tblEquipment
WHERE (((InStr([tblEquipment]![Equipment
Number],[Forms]![frmRoutLog]![chrBuildingNum] & '-'))<>'0'));
This works fine for all routes contained in one building. Howerever,
they've added a route covering equipment from two buildings. Is there a way
to amend the SQL in either the main form or subform such that if the route
covers two buildings (in this case, chrRoute = "PM6110", but they could end
up adding others) such that two buildings are selected. (Currently the cbo
picks equipment only in bld 19 when the route covers both building 19 and
building 20.)
Thanks
EQC
Click to expand...

chrRoute doesn't help. Is there somewhere a field holding "19 20"?
Maybe chrBuildingNum?

Suppose you have chrBuildingNum holding "19" or "19 20" or "19 20 21"
and so on with blanks between the numbers.
I'd have a solution for 1, 2 or more buildings but it would enormously
complicate the query: better with VBA
You need a function

function isBuilding(byval EqNum, byval BldgNum)
isBuilding = false
if right$(BldgNum,1)<>" " then BldgNum=BldgNum+" "
I=instr (BldgNum, " ")
while I>0
aTst=left$(BldgNum, I-1)
BldgNum=mid$(BldgNum, I+1)
if instr(EqNum, aTst+"-")>0 then
isBuilding = true
exit function
end if

I=instr (BldgNum, " ")
wend
end function

Then you change your last WHERE to:

WHERE isBuilding([tblEquipment].[Equipment
Number], [Forms]![frmRoutLog]![chrBuildingNum])=true;

This finds every record in tblEquipment where tblEquipment.[Equipment
Number] contains one of the numbers out of tblRoutes.chrBuildingNum.

But of course the query must call the function for every record in
tblEquipment. This is not beneficial for performance if there are many
records and people are in a hurry. You should consider the possibility
of restructuring the tables.

BTW why do you write tblEquipment]![Equipment Number] sometimes with !
and sometimes with dot?

Greetings
Marco P
 
P

Paul Shapiro

Marco Pagliero said:
We have a database used by maintenance personnel to record equipment
issues
discovered during a predictive maintenance round. The techs enter info
into
a form that has a subform; the source data for the main form comes from:
SELECT tblRoutLog.*, tblRoutes.chrBuildingNum
FROM tblRoutes INNER JOIN tblRoutLog ON tblRoutes.chrRouteID =
tblRoutLog.chrRout;
The subform (master field: idsRoute, Child Field chrRoute) has a combo
box
whose row source is:
SELECT tblEquipment.[Equipment Number], tblEquipment.[Equipment Name]
FROM tblEquipment
WHERE (((InStr([tblEquipment]![Equipment
Number],[Forms]![frmRoutLog]![chrBuildingNum] & '-'))<>'0'));
This works fine for all routes contained in one building. Howerever,
they've added a route covering equipment from two buildings. Is there a
way
to amend the SQL in either the main form or subform such that if the
route
covers two buildings (in this case, chrRoute = "PM6110", but they could
end
up adding others) such that two buildings are selected. (Currently the
cbo
picks equipment only in bld 19 when the route covers both building 19 and
building 20.)
Thanks
EQC
Click to expand...

chrRoute doesn't help. Is there somewhere a field holding "19 20"?
Maybe chrBuildingNum?

Suppose you have chrBuildingNum holding "19" or "19 20" or "19 20 21"
and so on with blanks between the numbers.
I'd have a solution for 1, 2 or more buildings but it would enormously
complicate the query: better with VBA
You need a function

function isBuilding(byval EqNum, byval BldgNum)
isBuilding = false
if right$(BldgNum,1)<>" " then BldgNum=BldgNum+" "
I=instr (BldgNum, " ")
while I>0
aTst=left$(BldgNum, I-1)
BldgNum=mid$(BldgNum, I+1)
if instr(EqNum, aTst+"-")>0 then
isBuilding = true
exit function
end if

I=instr (BldgNum, " ")
wend
end function

Then you change your last WHERE to:

WHERE isBuilding([tblEquipment].[Equipment
Number], [Forms]![frmRoutLog]![chrBuildingNum])=true;

This finds every record in tblEquipment where tblEquipment.[Equipment
Number] contains one of the numbers out of tblRoutes.chrBuildingNum.

But of course the query must call the function for every record in
tblEquipment. This is not beneficial for performance if there are many
records and people are in a hurry. You should consider the possibility
of restructuring the tables.

BTW why do you write tblEquipment]![Equipment Number] sometimes with !
and sometimes with dot?

Greetings
Marco P
Click to expand...

Adding to what Marco said, a better data structure would make the sql
simple. Your current design apparently assumed a route would only include
one building. Now that a route can include more than one building, the
appropriate design would change. I don't understand all your requirements or
the present design, but maybe something like:
Routes {routeID, routeName, routeDurationMinutes, etc.}
Buildings {buildingID, buildingName, buildingStreetAddress, etc.}
Rooms {buildingID (FK), roomNameOrNumber, floor, etc.}
RouteStops {routeID (FK), buildingID (FK), roomNameOrNumber (FK),
routeSequenceNumber, etc.}
 
Top