HELP PLEASE

[desperate in MS]

have a database in Access that I have entered vacation days in
A ID
B Name
C Craft
D Supervisor
Now get the though part...
E through say J have their chosen weeks for vacation
ie..E 3/23-26/2009....F 5/11-14-2009 and like that

Here I am lost...what I would like to do is have a report/form (something)
that I can display those folks and date for any givin Month. We are trying to
track how many folks we have out each week of each month. Maybe even a report
that will break out the months then weeks for the whole year? I could use ANY
suggestions out there.

 

[karl dewey]

You built a spreadsheet. Change it to a relational database using two tables
- People and Vacation_Plans.
Use a union query to convert the data --
SELECT ID, [E] AS [Vacation]
FROM YourTable
UNION ALL SELECT ID, [F] AS [Vacation]
FROM YourTable
UNION ALL SELECT ID, [G] AS [Vacation]
FROM YourTable
........
UNION ALL SELECT ID, [J] AS [Vacation]
FROM YourTable;

Then use a make table to build Vacation table. Remove E through J from
People table. Set a one-to-many relationship from People to Vaction on ID.
After that you can use a left join query from People to Vaction on ID.
Use form/subform for data entry/display of People/Vacation.

 

[desperate in MS]

You built a spreadsheet. Change it to a relational database using two tables
- People and Vacation_Plans.
Use a union query to convert the data --
SELECT ID, [E] AS [Vacation]
FROM YourTable
UNION ALL SELECT ID, [F] AS [Vacation]
FROM YourTable
UNION ALL SELECT ID, [G] AS [Vacation]
FROM YourTable
.......
UNION ALL SELECT ID, [J] AS [Vacation]
FROM YourTable;

Then use a make table to build Vacation table. Remove E through J from
People table. Set a one-to-many relationship from People to Vaction on ID.
After that you can use a left join query from People to Vaction on ID.
Use form/subform for data entry/display of People/Vacation.
--
KARL DEWEY
Build a little - Test a little

have a database in Access that I have entered vacation days in
A ID
B Name
C Craft
D Supervisor
Now get the though part...
E through say J have their chosen weeks for vacation
ie..E 3/23-26/2009....F 5/11-14-2009 and like that

Here I am lost...what I would like to do is have a report/form (something)
that I can display those folks and date for any givin Month. We are trying to
track how many folks we have out each week of each month. Maybe even a report
that will break out the months then weeks for the whole year? I could use ANY
suggestions out there.

I feel so stupid...you did this for me and I still can't figure it out...is
the above a Macro? I now have to databases...People and Vacation_Plan...Now I
am having a blonde moment....long moment...I'm not sure about
"union"...thanks for your help

 

[John W. Vinson]

I feel so stupid...you did this for me and I still can't figure it out...is
the above a Macro? I now have to databases...People and Vacation_Plan...Now I
am having a blonde moment....long moment...I'm not sure about
"union"...thanks for your help

What Karl is saying is that your table structure IS WRONG and needs to be
restructured; and he's offering you a Query to correct it.

Your database is designed as if it were a spreadsheet, with columns A, B, C
and so on. Excel is a spreadsheet; Access *isn't*.

You should have two tables, a table of Employees related one to many to a
table of Vacation. Karl's query will help you build and populate those tables.

For some introductions to how databases are designed to work see
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials