Query help!

[BILLATMHT via AccessMonster.com]

I am developing a small CMMS database. I have a table named PM SCHEDUAL, it
has one field for Equipment ID and 52 fields for work weeks i.e. (WW01,WW02,
WW03ect.). Under each work week field I have listed the inspection type for
that week on each unit. I am trying to create a single query that will
display only the field that matches the current work week -DatePart("ww",Date
())-so that i can run an append querie weekly to update my service records.
Is there a way I can select the field in the query using code in the field
box in design view? I dont want to seperate select and append queries for
each week.

 

[Amy Blankenship]

Your problem is the table design, not the query. If you use a table that
has a record for each work week rather than a column for each work week, the
query would be easy.

HTH;

Amy

 

[Bill foster via AccessMonster.com]

Amy
I have already created a seperate table that has a record for each work week
and a field for each equipment ID. The problem with that is that when I go to
create the append query to create the weeks service records I need both the
equipment ID and the inspection type to create a usable record.

Your problem is the table design, not the query. If you use a table that
has a record for each work week rather than a column for each work week, the
query would be easy.

HTH;

Amy

I am developing a small CMMS database. I have a table named PM SCHEDUAL, it
has one field for Equipment ID and 52 fields for work weeks i.e.

[quoted text clipped - 9 lines]

box in design view? I dont want to seperate select and append queries for
each week.

 

[Amy Blankenship]

Why don't you describe more in detail what you need the system to do? You
might want to move this discussion to the tabledesign newsgroup.

HTH;

Amy

Amy
I have already created a seperate table that has a record for each work
week
and a field for each equipment ID. The problem with that is that when I go
to
create the append query to create the weeks service records I need both
the
equipment ID and the inspection type to create a usable record.

Your problem is the table design, not the query. If you use a table that
has a record for each work week rather than a column for each work week,
the
query would be easy.

HTH;

Amy

I am developing a small CMMS database. I have a table named PM SCHEDUAL,
it
has one field for Equipment ID and 52 fields for work weeks i.e.

[quoted text clipped - 9 lines]

box in design view? I dont want to seperate select and append queries
for
each week.

 

[Bill foster via AccessMonster.com]

What I am trying to do is create cyclical preventive maintenance service
records in my database. The cycles are weekly, monthly, quarterly, tri-
annually, semiannually, and annually. There are several reasons I felt that
making a table that contained a unit field and 52 work week fields then
querying off it to append the service record table would be the simplest and
best way to go.
1.I want to be able to enter new units, and in a short period of time be able
to schedule their maintenance intervals. The types of inspections vary from
unit to unit; also the man hours required to complete them varies.
2.I want to be able to control the work per week so that I don’t end up with
80 hours of work scheduled for one week and 20 hours of work scheduled for
another.
3.I only want to generate a record if an inspection is due and then I only
want a record for that type of inspection.
4.I want to be able to query the table to convert the inspection types into
man-hours so that I can graph the weekly work load and maintain the annual
balance by staggering the cycles of newly entered units.
5.I want the schedule “set in stone” I do not want any migration of the cycle
do to calculated next due dates.

So here’s what I’ve done. I created a table called PM Schedule. It has one
unit field and
52 work week fields.

UNITNUMBER WW41 WW42 WW43 WW44
TC1 WK WK QRT WK
JETWAY1 WK SEMI WK WK
OB4 MTH WK WK WK

This allows me to quickly enter new units as my contract expands and control
the annual cycle to place the man-hours at the most efficient intervals. This
balances my weekly work load and it doesn’t migrate over any period of time.
It also eliminates the need for numerous calculations on date/ time. I don’t
have to worry that 52 doesn’t divide well by 3 or that every third monthly
cycle is five weeks long. I also don’t have to worry about generating a
weekly ,monthly, quarterly, and Semiannual work order for say Jetway1 in WW42
in the example, when all I need is the Semi. What I NEED is a Simple way to
query this table so that the query will show the unit number field values and
only the work week field values for the work week field that is equal to the
current work week. I don’t think this should be too hard but I’m not as good
at code as I wish I was.

Why don't you describe more in detail what you need the system to do? You
might want to move this discussion to the tabledesign newsgroup.

HTH;

Amy

Amy
I have already created a seperate table that has a record for each work

[quoted text clipped - 21 lines]

 

[Amy Blankenship]

I don't think you can do what you want with the data structure you have.
The problem is that the data structure you need:

WorkWeeks:
WorkWeekID
WorkWeekName

Inspections
WorkWeekNumber
UnitNumber
InspectionTypeID

InspectionType
InspectionTypeID
InspectionTypeName
InspectionHoursNeeded
OtherInspectionInfoThatMayBeRelevant

Unit
UnitNumber
UnitName
OtherUnitInfo

(You'd also need a table for inspections completed, if you want to record
that, but that's an aside from your question)

Is very hard to get access to represent with forms in Access. I imagine the
reason you've gone to all one table is that it's easy to represent all 52
weeks on the page at one time in the form when that's what you do, even if
there aren't any records for the weeks yet.

Unfortunately this is just a limitation of the tool you can't get around
without some heavy duty coding and querying. However, you can't justify a
data design that can't be queried in the way you've needed just because you
have a hard time writing forms to get the data *IN.* There is no point
making it easy to get the data in if you can't get it back out.

So I guess you have several choices:

1) leave the data structure as it is, and write a separate query for each
week.
2) look for an off the shelf product that does what you want
3) change the data structure, but be ready to get your hands dirty with some
serious code
4) find a consultant who can change the data structure and write the forms
that work like the one that feeds your current structure

HTH;

Amy

What I am trying to do is create cyclical preventive maintenance service
records in my database. The cycles are weekly, monthly, quarterly, tri-
annually, semiannually, and annually. There are several reasons I felt
that
making a table that contained a unit field and 52 work week fields then
querying off it to append the service record table would be the simplest
and
best way to go.
1.I want to be able to enter new units, and in a short period of time be
able
to schedule their maintenance intervals. The types of inspections vary
from
unit to unit; also the man hours required to complete them varies.
2.I want to be able to control the work per week so that I don't end up
with
80 hours of work scheduled for one week and 20 hours of work scheduled for
another.
3.I only want to generate a record if an inspection is due and then I only
want a record for that type of inspection.
4.I want to be able to query the table to convert the inspection types
into
man-hours so that I can graph the weekly work load and maintain the annual
balance by staggering the cycles of newly entered units.
5.I want the schedule "set in stone" I do not want any migration of the
cycle
do to calculated next due dates.

So here's what I've done. I created a table called PM Schedule. It has one
unit field and
52 work week fields.

UNITNUMBER WW41 WW42 WW43 WW44
TC1 WK WK QRT WK
JETWAY1 WK SEMI WK WK
OB4 MTH WK WK WK

This allows me to quickly enter new units as my contract expands and
control
the annual cycle to place the man-hours at the most efficient intervals.
This
balances my weekly work load and it doesn't migrate over any period of
time.
It also eliminates the need for numerous calculations on date/ time. I
don't
have to worry that 52 doesn't divide well by 3 or that every third monthly
cycle is five weeks long. I also don't have to worry about generating a
weekly ,monthly, quarterly, and Semiannual work order for say Jetway1 in
WW42
in the example, when all I need is the Semi. What I NEED is a Simple way
to
query this table so that the query will show the unit number field values
and
only the work week field values for the work week field that is equal to
the
current work week. I don't think this should be too hard but I'm not as
good
at code as I wish I was.

Why don't you describe more in detail what you need the system to do? You
might want to move this discussion to the tabledesign newsgroup.

HTH;

Amy

Amy
I have already created a seperate table that has a record for each work

[quoted text clipped - 21 lines]

for
each week.

 

[Bill foster via AccessMonster.com]

Thanks Amy
I will just do 52 querys with 52 append querys and create a form PM Generator
with 52 comand buttons.

I don't think you can do what you want with the data structure you have.
The problem is that the data structure you need:

WorkWeeks:
WorkWeekID
WorkWeekName

Inspections
WorkWeekNumber
UnitNumber
InspectionTypeID

InspectionType
InspectionTypeID
InspectionTypeName
InspectionHoursNeeded
OtherInspectionInfoThatMayBeRelevant

Unit
UnitNumber
UnitName
OtherUnitInfo

(You'd also need a table for inspections completed, if you want to record
that, but that's an aside from your question)

Is very hard to get access to represent with forms in Access. I imagine the
reason you've gone to all one table is that it's easy to represent all 52
weeks on the page at one time in the form when that's what you do, even if
there aren't any records for the weeks yet.

Unfortunately this is just a limitation of the tool you can't get around
without some heavy duty coding and querying. However, you can't justify a
data design that can't be queried in the way you've needed just because you
have a hard time writing forms to get the data *IN.* There is no point
making it easy to get the data in if you can't get it back out.

So I guess you have several choices:

1) leave the data structure as it is, and write a separate query for each
week.
2) look for an off the shelf product that does what you want
3) change the data structure, but be ready to get your hands dirty with some
serious code
4) find a consultant who can change the data structure and write the forms
that work like the one that feeds your current structure

HTH;

Amy

What I am trying to do is create cyclical preventive maintenance service
records in my database. The cycles are weekly, monthly, quarterly, tri-

[quoted text clipped - 66 lines]