Yearly report query plus

[Gabriella777_2]

Okay I know this should be simple but I need help.

I have a main table of machine malfunction report entries.
It consists of the machine name, the repair requestors initials, the
repairers initials, the request and repair dates, request time(when did
machine stop working), machine down time (how long out of service), labor
time, the problem and repair.

All I want to do is create a query that will be used to create a report the
shows the details for each year and can be, if needed, based on a separate
machine.

I can create a msgbox that asks for the request and repair dates (full
date), but I haven't figured out how to be able to have them simply enter the
year instead of the whole business of both dates.

Also, I want to create msgbox that will allow them to select a specific
machine (either by entering the information manually or doing a drop down
that draws the information from the table itself - with multiple selection
capability) or to do something that selects them all - as needed.

Lastly, I want the option to summarize the information in the down & labor
times, the problem and repair date, et cetera by named machine. (ie. How much
of the year was the 802 machine down?, How much of the year was spent
repairing the N2 or N4 Tipton? Who was the most popular requestor/repairer on
this machine? What were the most common problems found on the 1801 and/or the
. . . .)

Okay so maybe - not so simple . . .

If anyone can walk me through it, I would appreciate the help.

 

[Dale Fye]

Gabriella,

1. You might use something like:

SELECT *
FROM yourTable
WHERE Year([RequestDate]) = 2008
OR Year([RepairDate]) = 2008

or

SELECT *
FROM yourTable
WHERE Year([RequestDate]) = Year(Date())
OR Year([RepairDate]) = Year(Date())

2. To select which machine, I think I would recommend a multi-select
listbox to display the list of machines, using the first column as the
MachineID (you can probably hide this column). If you do this, you will need
to write some code to concatenate the MachineID values into a criteria that
looks something like:

WHERE [MachineID] IN (12,23, 19, 24)

to do this, you will need to use the listboxes SelectedItems method.
Something like:

Dim varItem as variant
Dim strCriteria as string

For each varItem in lst.SelectedItems
strCriteria = "," & lst.column(0, varItem)
Next varItem
strCriteria = mid(strCriteria, 2)

3. As to your last question, I would create a series of reports, then use
the criteria in your custom messagebox to define the criteria for each report.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.