Macro to rename a table

[Guest]

Hello,

I have this. Weekly payroll based on a templated table. I take the table and
"make table query" into "current week" table. I make edits as needed in that
table. I need a copy of that table for historical data. I rename that table
and run weekly report off of final table.

Questions:
I need a way to shut off prompts as I am going to run the first few things
with a macro.
I need a way to use a macro to rename a table to a name that I can type in
at run time.

Thanks as always.

JC
PAPI

 

[Jeff Boyce]

Joe

It would be a highly unusual database design that required changing table
names. If you'll provide more specifics about what you are trying to
accomplish, the newsgroup readers here may be able to offer approaches get
your job done.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hey Jeff,
Thanks for the response.

I am working on a payroll system. The employee list is so fluid that I can't
find any way to keep up with the changes. For example there are 100 static
employees, there is an endless pool of variable employees. In any given week
I will have between 100-110 people that need to be paid. The static employees
have overtime pay one week and possible vacation, sick day, or, and I quote,
I just wanted to give him an extra $100. On top of all of that we have
situations where an employee may get partial pay, workman's comp.... so all
of those scenerios have led me to the conclusion that I can have a base table
of employee data that I can copy with a macro command to a "this week temp
table" I will then make all the modifications needed. I can then take that
table and programically (new word??) copy it to the "this week's date" table.
My problem at this point is how do I rename it? I was thinking that when I
execute a macro to create the table I could either rename it as part of the
query that creates it (make table) or as part of the macro that calls the
make table query in the first place.

Any ideas?
Thanks
Joe C
Papi

 

[Steve Schapel]

Joe,

I regret to say that your idea of creating a separate table for each
week's pays, is an invalid one from a database design point of view.

You need one table to list employees. This would include the statics
and the variables. It does not matter that some of these people only
work for 1 week and then disappear. There is no reason not to simply
retain them in the Employees table.

And then you need another table for payments. This will be related
one-to-many to the Employees table. Each week, you will use a form to
enter all of the payments made. I would imagine this would oftem
involve more than one entry for a given employee, for example an entry
for standard hours and an entry for overtime. No problem.

With a data structure like this, it will be very easy to use a query to
produce summary or total figures for each/any employee for any selected
pay date.

 

[Guest]

Steve,

I am working this out as I go here. I guess my goal is to not add all 100 +
people every week. I could have the base table copy that to a temp table,
then append that to the main data table. Does that make more sense??

 

[Jeff Boyce]

Joe

I'm with Steve on this...

Why are you making multiple copies (i.e., tables)? You might need to do it
that way if you were working with a spreadsheet.

Access is a relational database -- you won't get very much use of the
features and functions if you insist on feeding it 'sheet data.

Step back from your PC for a bit and (re-)consider the data structure. If
"normalization" and "relational" don't mean much, spend a bit of time
reading up on them. In Access, everything starts with the data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Guys,

I understand the concept. I am trying to eliminate the need to enter the 85
or so people's information. Every week 100 people get paid. 15 have
alterations to the norm. How do I create a scenerio where a person's annual
salary can be totaled with an increase on week 15 and a decrease on week 19
and a partial on week 40..... for 100 people?

I will have to total week 1-15 then total 16-19 then include week 40 then go
back to the rate for 16-19? Then also include OT.... Then be able to call up,
for review, week 22.

Thanks.

JC

 

[Steve Schapel]

Joe,

Table: Emplyees
- EmployeeID
- Surname
- FirstName
- ...other stuff about the employee

Table: Payments
- PaymentID
- EmployeeID
- PayWeek
- PayType
- Amount
- Comment

Sample data:

Employees:
1 Boyce Jeff
2 Jump Skippy

Payments:
1 1 15 Normal $222
2 1 15 Overtime $111
3 2 15 Normal $123
4 1 16 Normal $222
5 1 16 Deduction -$50 broke something
6 2 16 Normal $23
7 2 16 Sick Leave $100
....

As I said before, with a data structure like this, it will be very easy
to use queries to produce summary or total figures for each/any employee
for any selected pay date/s.

Similarly, using properly constructed forms, the entry and accessing of
the required data can be automated and simple.

 

[Guest]

Steve,

This table structure doesn't address the main issue. I have 123 different
people to do this for. I don't want to have to enter data for people who do
not have a variation on their norm. What do you suggest for the people who
have the same data as the week before?

To be clear, 123 people, a set of people do something that changes their
weekly pay, never the same people, never the same change, always a
significant group who do not change from the week before.

Don't want to do data entry 52 times on 123 +/- people during a given year.

What do you think?
JC

 

[Jeff Boyce]

Joe

I'm missing something here...

Why would you need to "update" the table for people whose information did
NOT change?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Ok, the end result is at year end (or at any point in the year when we want a
YTD total) when we have to total for the year. At that point we need to be
able to provide a summary of all the weeks, a grand total for OT, etc. Thus I
have to have a week by week account of the payroll.

Is that any clearer?

JC

 

[Steve Schapel]

Joe,

You're jumping the gun here. You're talking about the management of the
data, the procedures and workload for entering it, etc. When you get
confused about databases, the best thing is to just focus on getting the
table structure right first. Put all thoughts of how to do it out of
your head, you can cross that bridge when you come to it. In the
meantime, I will assure you, though, that there are ways of expediting
the data entry for those employees whose pay is "norm" on any given
week. But there's no way you can avoid recording the required data.

 

[Guest]

Steve,

Let's suppose I have the table structure in place, how do I enter "norm
data" automatically? This is the basic issue at hand.
JC

 

[Guest]

Well, it looks like you huys have run out of ideas for ths project. I
appreciate your help. Catch you next time.

JC

 

[Steve Schapel]

To be honest, Joe, you didn't respond to the ideas that were given.

 

[Guest]

Steve,

I have posted what seems like a hundred questions on these boards and every
response has helped me in some way. In this particular problem I needed to
way to stream line data entry. Unfortunatly I did not get a workable solution
to that issue.
That being said this is what I ended up with.
I have a template table. Every week I execute a make table query that will
copy that template and create a "current week" table. I have my payroll
people modify that table as needed. From there I use an append query to
append that data to a fy'08 table.

Your help has been immeasurable in many instances on this board. I
appreciate your services and expertise. Please advise if there is a different
method of doing the above.

Thanks.
GO Sox!!!

JC