Query using several of the same tables

[Bas Versteegen]

This might be a stupid question but I am quite new to the program and I am
still trying to figure it out.

I have the following problem:

I have a table with employees. These employees perform several jobs a month
for which they get payed (per job). The table "Employees" has all the
necessary info in it that regards the employees.

Furthermore I have several tables called "January 2009" "February 2009" etc.
containing all information of the jobs and the employee number, which is also
in the employees table.

What I want to do is run a query that shows me all the jobs that one
employee has done over the past few months. I have figured out how to make
tun a query for one month, but can I make a query that includes several
months? The number of jobs is too large to put them all in one table.

Thanks in advance!

 

[John W. Vinson]

This might be a stupid question but I am quite new to the program and I am
still trying to figure it out.

I have the following problem:

I have a table with employees. These employees perform several jobs a month
for which they get payed (per job). The table "Employees" has all the
necessary info in it that regards the employees.

Furthermore I have several tables called "January 2009" "February 2009" etc.
containing all information of the jobs and the employee number, which is also
in the employees table.

That was your mistake! Storing data in a tablename is simply wrong, for
exactly the reason you're finding.

What I want to do is run a query that shows me all the jobs that one
employee has done over the past few months. I have figured out how to make
tun a query for one month, but can I make a query that includes several
months? The number of jobs is too large to put them all in one table.

What? Tens of millions of jobs? Your employees are very busy!

Dates *are data* and should be stored in a date/time field in a single jobs
table. What is the actual structure of your [January 2009] table - fieldnames,
datatypes, relationships?

 

[Jerry Whittle]

You have multiple tables doing what should be in one table. There's the rub.
Your best bet would be to put all the Jobs data into one table with a
date/time field so that you can figure out stuff like "what month" as needed.

To use your existing setup, you'll need to create a Union All query that
joins all the Jobs tables. This can be a problem, slow, and you need to
remember to add new months to it. It would look something like below. You'll
need to save this as a named query and use it as a table in a query with the
Employees table.

Select * from [January 2009]
UNION ALL
Select * from [February 2009]
UNION ALL
Select * from [March 2009] ;

If you changed the table structure between the months, such as added a
field, then the above won't work. You'll need to manually add in each needed
field.

 

[Bas Versteegen]

This might be a stupid question but I am quite new to the program and I am
still trying to figure it out.

I have the following problem:

I have a table with employees. These employees perform several jobs a month
for which they get payed (per job). The table "Employees" has all the
necessary info in it that regards the employees.

Furthermore I have several tables called "January 2009" "February 2009" etc.
containing all information of the jobs and the employee number, which is also
in the employees table.

That was your mistake! Storing data in a tablename is simply wrong, for
exactly the reason you're finding.

What I want to do is run a query that shows me all the jobs that one
employee has done over the past few months. I have figured out how to make
tun a query for one month, but can I make a query that includes several
months? The number of jobs is too large to put them all in one table.

What? Tens of millions of jobs? Your employees are very busy!

Dates *are data* and should be stored in a date/time field in a single jobs
table. What is the actual structure of your [January 2009] table - fieldnames,
datatypes, relationships?

Thank you for the answer. I think I see what you mean. The problem that
occured when putting all single jobs in one table was the speed. This is
probably a computer issue and might be solved with a faster computer I guess.
Another question: Will the speed of queries improve when data is divided over
more tables or will this slow things down?

Currently I only have 2 types of tables. One for the jobs and one for the
employees. The employees table includes:

Job ID
Job Pay
Bonus Pay
Job Expenses
Special Expenses
Total Job Expenses
Job Date
Client Name
Client project name
Job Location Name
Job Location Address
Job Location City
Employee ID

The Employees table includes:

Employee ID
First Name
Last Name
Address
Postal Code
City
Country
Phone Number
E-mail Address
Date of Birth
Bank Account number

Primary keys are Job ID and Emplyee ID and the relation is the Employee ID.

Come to think of it I guess it would probably be better to split up the info
in the jobs table in more tables such as clients and locations. Can you let
me know if I am on the right track?

Thanks!

 

[John W. Vinson]

Thank you for the answer. I think I see what you mean. The problem that
occured when putting all single jobs in one table was the speed. This is
probably a computer issue and might be solved with a faster computer I guess.

Much more likely you need better query design and better indexes. Access is
QUITE capable of processing queries against ten-million-row tables. Moving the
backend into SQL/Server gives you even more power (and with SQL/Express it's
not even any extra cost).

Another question: Will the speed of queries improve when data is divided over
more tables or will this slow things down?

Going to multiple tables will seriously DEGRADE performance.

Currently I only have 2 types of tables. One for the jobs and one for the
employees. The employees table includes:

Job ID
Job Pay
Bonus Pay
Job Expenses
Special Expenses
Total Job Expenses

The Total Job Expenses field should not exist in your table; it should be
calculated on the fly in a query. In fact I'd suggest a separate expenses
table:

Expenses
ExpenseID <Autonumber, Primary Key>
JobID <foreign key to Jobs
ExpenseType
Expense <currency>
ExpenseDate <Date/Time>

Job Date
Client Name
Client project name
Job Location Name
Job Location Address
Job Location City
Employee ID

Each job involves one, and only one, ever, employee? Sounds a bit iffy to me!

The Employees table includes:

Employee ID
First Name
Last Name
Address
Postal Code
City
Country
Phone Number
E-mail Address
Date of Birth
Bank Account number

That looks ok, though I'd certainly be reluctant to have my bank account
information in somebody else's database.

Primary keys are Job ID and Emplyee ID and the relation is the Employee ID.

Come to think of it I guess it would probably be better to split up the info
in the jobs table in more tables such as clients and locations. Can you let
me know if I am on the right track?

Absolutely. Each type of "Entity" - real-life person, thing or event, such as
a Client, a Location, an Expense - should have its own table.