append table code question

[JWCrosby]

I have a table of 25 employees (tblEmployees), each with an EmployeeID field
(primary key) and first name, last name, etc. fields.

Another table (tblPayrollRecords) holds monthly data on each employee,
linked on the EmployeeID field in a one-to-many relationship. One of the
fields in this table is named "Month".

Since I'm building the database, I'd like to "seed" the latter table and
create 12 records for each employee; 1 record for each month of the year.
Therefore, the Month field would have "Jan", "Feb", "Mar", etc. I would end
up with 300 records in the table (25 employees with a record for each month
of the year).

So, I need coding for an append query. Must be some sort of a loop I would
think.

Can anyone help me?

Thanks in advance.

Jerry

 

[John Vinson]

I have a table of 25 employees (tblEmployees), each with an EmployeeID field
(primary key) and first name, last name, etc. fields.

Another table (tblPayrollRecords) holds monthly data on each employee,
linked on the EmployeeID field in a one-to-many relationship. One of the
fields in this table is named "Month".

Rename it. MONTH is a reserved word, for the builtin Month() function.
And if (as is evident below) the month is a three-letter text string,
you're going to find that your pay dates sort in the order Apr, Aug,
Dec, Feb... alphabetically, that is. In addition... what will you do
three months from now? Is Jan January 2006, January 2007, maybe
January 2002 or 2009?

Since I'm building the database, I'd like to "seed" the latter table and
create 12 records for each employee; 1 record for each month of the year.
Therefore, the Month field would have "Jan", "Feb", "Mar", etc. I would end
up with 300 records in the table (25 employees with a record for each month
of the year).

Well... this is generally A Bad Idea. Creating empty "placeholder"
records, with just the employeeID and the month name, is *not* a good
approach! You'll have difficulty finding and updating the pay field.

Instead, I'd suggest using a date/time field. You can fill it in very
easily on a Form if you use a combo box based on a table with the next
three or four years of payroll dates. Fill in the date when you have
the other data for the field! There is NO advantage to having the
record there to start with.

So, I need coding for an append query. Must be some sort of a loop I would
think.

If you REALLY REALLY want to do it wrong, create a table with one
field PayMonth; fill it manually with values Jan, Feb, Mar, ..., etc.
Create a Query by adding your employee table and this table, with no
Join line. You'll get twelve rows for each employee. Change it to an
Append query and run it.

But as I say... *this is a bad design* and you have better ways to
accomplish your goal!

John W. Vinson[MVP]

 

[JWCrosby]

Thanks so much for taking the time to compose an answer for me. As is
usually the case with such limited means of communication, there's always
"the rest of the story" as Paul Harvey so aptly says. My responses below...

Rename it. MONTH is a reserved word, for the builtin Month() function.
And if (as is evident below) the month is a three-letter text string,
you're going to find that your pay dates sort in the order Apr, Aug,
Dec, Feb... alphabetically, that is. In addition... what will you do
three months from now? Is Jan January 2006, January 2007, maybe
January 2002 or 2009?

Actually, the name I used is "MonthID". For simplicity's sake I shortened
it in my submission. Didn't realize I had offended a sacred word.

I'm aware of the sorting issue and have a separate numbered field that I use
for sorting.

I don't plan to have more than one year's worth of data in a table. I'll
create new blank tables each January and can delete the old. The database is
primarily a worksheet that runs through some rather complex computations to
give us a salary figure that is then used in our bookkeeping/payroll software.

Well... this is generally A Bad Idea. Creating empty "placeholder"
records, with just the employeeID and the month name, is *not* a good
approach! You'll have difficulty finding and updating the pay field.

Maybe a bad idea from your standpoint, but perhaps a practical and workable
idea for my application. I intend to have all 12 records visible at the same
time in a continuous form. Because of how I'm using it, I won't have need to
"find and update" anything.

Instead, I'd suggest using a date/time field. You can fill it in very
easily on a Form if you use a combo box based on a table with the next
three or four years of payroll dates. Fill in the date when you have
the other data for the field! There is NO advantage to having the
record there to start with.

I had already considered this approach, and may still end up there. But a
question here: if I use a date/time field, and I want to just use the
3-letter Months, how do I do that so it still sorts properly?

If you REALLY REALLY want to do it wrong, create a table with one
field PayMonth; fill it manually with values Jan, Feb, Mar, ..., etc.
Create a Query by adding your employee table and this table, with no
Join line. You'll get twelve rows for each employee. Change it to an
Append query and run it.

Now THAT is the answer I was seeking! I didn't know how to do it, but you
explained it well. Thanks. I'll think through and consider your other
advice and it's applicability to my little program.

 

[John Vinson]

I had already considered this approach, and may still end up there. But a
question here: if I use a date/time field, and I want to just use the
3-letter Months, how do I do that so it still sorts properly

If you use a date/time field it WILL sort properly (without any
auxiliary number fields). You can set the Format of a textbox
displaying the field to "mmm" in order to display #6/10/2006# as
"Jun".

It's EASIER to sort dates properly than it is to sort text string
names of months.

John W. Vinson[MVP]

 

[JWCrosby]

Thanks, John, that's helpful.

Jerry

If you use a date/time field it WILL sort properly (without any
auxiliary number fields). You can set the Format of a textbox
displaying the field to "mmm" in order to display #6/10/2006# as
"Jun".

It's EASIER to sort dates properly than it is to sort text string
names of months.

John W. Vinson[MVP]