Querying Access 2013 Tables for Specific Field Information

[shishkish]

Hi, I'm new to Access 2013 and I would like to ask a question about querying field information across 52 tables. I have an overall staff list I have organized into positions with seniority. I also queried each position by seniority in their own list. I have created 52 tables representing 52 weeks of 2017. I am trying to create a simple vacation scheduling system. Am I able to query a search for an employee's ID number across 52 tables? If so, would I also be able to organize this data on a new table so I could print out a report for all the 2017 vacations for that one ID number? Lastly, I have the day of the week and the date listed in the top field header on each week table. Would these headers be able to be listed also in the final report to indicate what day and date a person is on vacation? Thank you for your time.

[CJ_London]

having a table for each week is bad db design. You should have one table with an additional 'weekno' column. Then your search becomes very easy

[Homegrownandy]

Perhaps make a more detailed post explaining what you want to achieve to get ideas how to get there. Rather than perusing a bad design that is.

I'm sure you will get some good feedback/ideas if you ask.

[shishkish]

Perhaps make a more detailed post explaining what you want to achieve to get ideas how to get there. Rather than perusing a bad design that is.

I'm sure you will get some good feedback/ideas if you ask.

I need to be able to print out a weekly report for every week of 2017 from Sunday to Saturday with the date to indicate the employees that are on vacation during that time frame. I tried the one table idea but I could not figure out how to make multiple header fields to indicate the day and date above the employees that are on vacation for a specific day. Thanks again for your help.

[aytee111]

Get the table design right first - this is not Excel!

Table 1 - employees
Table 2 - employee id, vacation from date, to date

VBA function:
- start date
- read table 2 record by record
- for every day, output a record to a temporary table:
- employee id
- date
- on vacation yes

You will end up with a table which contains all dates where someone(s) is on vacation. When you print the calendar you will access this table and be able to bring in an employee's name for that day.