help with a query

[danfryp]

I currently have a group of queries appending employee timesheet data from
linked excel tables into one main table.

I have set it up so that all these queries run under one macro, idealy this
macro will be run once a month to add that months timesheet data into the
main table.

I have created a new field in the main table called 'Month' and I want the
query to ask me each time it is run what month to input into this field. I
cannot use the 'Date the record is created' function as I will be inputing
data from the past year. Hopefully I have explained this properly and any
help would be greatly appreciated.

Many Thanks

Dan

 

[Ken Sheridan]

Dan:

If your main table spans more than one year you'll need a column for the
year as well or there will be no way of distinguishing the rows for the same
month in different years.

First create a dialogue form with controls in which to enter the year and
month. Add a button to call the macro which runs the queries. Then in each
'append' query add references to the controls. e.g.

INSERT INTO MainTable
(WorkYear, WorkMonth, SomeField, SomeOtherField)
SELECT Forms!YourForm!txtYear,
Forms!YourForm!txtMonth,
SomeField, SomeOtherField
FROM LinkedTable;

I'd advise against using Month or Year as field names. They are the names
of built in functions in Access, so are best avoided as object names. use
more specific names as I've done in the example above.

You might prefer to use combo boxes for the year and month rather than text
boxes; you can then give each a value list as their RowSource properties,
including whatever range of years you want to cover for one, and the 12
months of the year for the other. If the WorkMonth column is a number, 1 –
12, then you can set up the combo box to show the month names bur enter a
number into the table by setting up its RowSource as:

1;January;2;February;………….11;Novenber;12December

Filling in the gap for March – October of course. Set its other properties
like so:

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England