Append Query Question

[RRLady]

I have created an append query to add table entries from data summarized in a
query.

The query takes daily information and sums it to a monthly total. I need
the monthly total to be input to the table.

The append query works; however, I would like for the query to create a
field entry for the table that shows the month and year (June 2005) for the
months that was summarized.

Is this possible?

Thanks for your help.

 

[John Vinson]

I have created an append query to add table entries from data summarized in a
query.

The query takes daily information and sums it to a monthly total. I need
the monthly total to be input to the table.

The append query works; however, I would like for the query to create a
field entry for the table that shows the month and year (June 2005) for the
months that was summarized.

Is this possible?

Several ways, in fact.

One would be to add a Text field to the target table, and append

Format([datefield], "mmmm yyyy")

to it. This will take the date field in your totals query and convert
it to a text string in that format.


John W. Vinson[MVP]

 

[RRLady]

The query that compiles the data to be appended is created by summing groups
of records to get the daily activity. The date range has a Where statement
on the Total Row, which means the date is not added to the answer.

So I don't even get a date in the subset of data from the query.

Can you suggest another way around the Where clause?

I'm kinda stuck with this...

Thanks!

I have created an append query to add table entries from data summarized in a
query.

The query takes daily information and sums it to a monthly total. I need
the monthly total to be input to the table.

The append query works; however, I would like for the query to create a
field entry for the table that shows the month and year (June 2005) for the
months that was summarized.

Is this possible?

Several ways, in fact.

One would be to add a Text field to the target table, and append

Format([datefield], "mmmm yyyy")

to it. This will take the date field in your totals query and convert
it to a text string in that format.


John W. Vinson[MVP]

 

[John Vinson]

The query that compiles the data to be appended is created by summing groups
of records to get the daily activity. The date range has a Where statement
on the Total Row, which means the date is not added to the answer.

So I don't even get a date in the subset of data from the query.

Can you suggest another way around the Where clause?

No, given that I have no idea how your table is structured nor where
this date might be found. Could you post the SQL of your current
query, and perhaps indicate what date it is that you want shown?

John W. Vinson[MVP]

 

[RRLady]

Okay, here is the SQL code from the append query.

INSERT INTO tblMonthlyHoursAndActivitesByOfficer ( UnitID, WorkStatusCode,
TotalOfficerHours, TotalOfficerActivities )
SELECT tblOfficerList.UnitID, tblDailyWorkLog.WorkStatusCode,
Sum([PatrolDutyHours]+[VehicleCrashInvestDutyHours]+[CaseReportslDutyHours]+[CourtDutyHours]+[CriminalInvestigationDutyHours]+[MealBreakDutyHours]+[OfficeDutiesDutyHours]+[TrainingDutyHours])
AS TotalOfficerHours,
Sum([TrafficArrestsActivity]+[DuiArrestsActivity]+[WarningsActivity]+[CallsForServiceActivity]+[VehicleCrashesInvestPropertyDamageActivity]+[VehicleCrashesInvestPersonal
InjuryActivity]+[VehicleCrashesInvestFatalityActivity]+[VehicleCrashArrestsActivity]+[CriminalArrestsActivityMisdemenor]+[CriminalArrestsActivityFelony])
AS TotalOfficerActivities
FROM (tblDailyWorkLog INNER JOIN tblOfficerList ON tblDailyWorkLog.UnitID =
tblOfficerList.UnitID) INNER JOIN tblWorkStatusCodes ON
tblDailyWorkLog.WorkStatusCode = tblWorkStatusCodes.WorkStatusCode
WHERE (((tblDailyWorkLog.Date) Between [Enter First Day of Reporting Month]
And [Enter Last Date of Reporting Month]))
GROUP BY tblOfficerList.UnitID, tblDailyWorkLog.WorkStatusCode
ORDER BY tblOfficerList.UnitID, tblDailyWorkLog.WorkStatusCode;

Here is an example of the data that is returned by the query.

Unit ID Number Work Status Total Officer Hours Total Officer Activities
1234 A 66.75 90
1234 B 0 1
1582 A 18.5 2
1582 B 0 0
1582 C 0 0
2532 A 28.75 41
2532 D 0 0
3456 A 8.5 17
401 A 10 19

What I am missing is a date field. I want it to show the Month and Year
that the data is from. For example, May 2005, June 2005.

The dates are added by the user from the parameters added to show the Start
and End Dates for the month.

Thanks again.

 

[John Vinson]

What I am missing is a date field. I want it to show the Month and Year
that the data is from. For example, May 2005, June 2005.

Since every value of the (badly misnamed, since it's a reserved word)
field Date will have that month and year - assuming that the user puts
in valid criteria - simply add a field by typing

Format(tblDailyWorkLog.Date, "mmmm yyyy")

in a vacant Field cell; set its Total line to Expression.

You may want to consider a criterion of

= DateSerial(Year([Enter a date in the desired month:]), Month([Enter a date in the desired month:], 1) AND < DateSerial(Year([Enter a date in the desired month:], Month([Enter a date in the desired month:]) + 1, 1)

The user won't need to count months to determine the last day, and
need only enter a single date anywhere within the desired month.

John W. Vinson[MVP]

 

[RRLady]

I don't mean a literal field called Date.

The table the data will be appended to is called
tblMonthlyActivitesAndHoursByOfficer.

The append query is called qappTotalHoursAndActivitiesByOfficer.

The examples of the SQL code and data samples were from the append query.

My table tblMonthlyActivitesAndHoursByOfficer contains a text field named
MonthYear that will be used to store the data I'm trying to create. (May
2005, June 2005, etc.).

I tried adding the field you suggested to the append query, but that doesn't
return anything.

The dates I am using to Group By, which are inserted in the paramaters when
the query starts, are stored in the table tblDailyWorkLog. This allows the
user to select only the records for the desired month, and sum them up.

I don't want to add anything to tblDailyWorkLog.

I'm not counting anything either.

When the query runs, it is creating data that I need in the table. I need
for it to create the data for the field MonthYear.

Thanks,

RRLady

 

[John Vinson]

When the query runs, it is creating data that I need in the table. I need
for it to create the data for the field MonthYear.

If you do as I suggested, that is exactly and precisely what you will
get.

Did you try it? or just reject the idea out of hand?

John W. Vinson[MVP]

 

[RRLady]

John,

I tried it again. It did work this time, but I could not use the criterion
statement. It just keeps returning an error in the expression.

However, it works without it. AND that's what I need.

Thanks for your help!

SMILES!

 

[laiyi1018]

misorw a