"Name" for a Create table query

[Dan2kx]

Hello to all that read
and thank you to all that post

Background: I have been tasked to create a holiday database for the employees in my lab, (so expect many more posts)

im stuck at the first hurdle,

i want to have a table with emplyee details, and to create a new table for each employee to include holiday dates and ammounts etc, so i need a create table query to creat a brand new (unrelated query) for each employee.

in the "staff" table there are the details:
name
dob
job title
salary
hours PW
holiday entitlement

and so on

and in the Holiday table:
Holiday id
staff id (to link, to table (each member has own table so this field is always filled with same staff id)
date
am
pm

so i want to create this holiday table for each employee added to the DB and Name the table using the StaffID,
which is where i am stuck

how can i use the creat table query to call it the staffid field??

please help me....

i also want to be able to crosstab from multiple tables (many many many tables (for each staff member) to create holiday lists and counts and whatever else... is this possible??? i flippin hope so, no need to tell me how (just yet) but can it be done??

thank you all so much
hope someone can help!!

(sorry for the massive post!)

DAN

[Rabbit]

That's not the usual design for something like this. Why do you need a different table for each employee? Usually you just have one holiday table and include all the employees in it.

[Dan2kx]

because i felt that would be the easiest way of compiling the data , (now i am having new thoughts (probly the sme thoughts as urself)) if i put in a staff id column that would update (the table would get very big wih all the changes) but so what,

ok im on ur level, i will have to test 2moro, but could you tell me how to make an update query input all the dates inbetween a range?

for example, "select holiday dates from - till - end date" i think that would be the easiest way to collate data but how can i get an update query to input dates "in between" those selected??

thnks for your help so far

[Rabbit]

The SQL syntax for returning records between 2 values is:

SELECT *
FROM Table1
WHERE DateField BETWEEN [Date1] AND [Date2];
 

Where Date1 and Date2 is to be replaced with how you want to specify the dates.

I'm not sure what you mean by "input dates between those selected".

[Dan2kx]

thats what i meant TBH
on the form i will have holiday dates FROM - TO (or something similar)
and i wated to create entries between, like defining a range

thanks for the help

[NeoPa]

Dan,

You may find (Normalisation and Table structures) & (How to structure your tables) helpful (I hope so).

[Dan2kx]

i dont think you understand my question what i wanted to do originally was use a variable to define the table names from a make table query,

i have scapped that idea and now want to know how to out put dates between those in two textboxes

i.e.

txtboxa = 22/10/07
txtboxb = 30/10/07

so i want my query to output data for each date in between

but i think i am going to have to crack recordset to do this

[Rabbit]

Wait.. so you just want to return entries? You keep saying create which is confusing me.

SELECT *
FROM Table1
WHERE DateField BETWEEN Forms!FormName!ControlName AND Forms!FormName!ControlName;
 

[Dan2kx]

On the userform i want the user to pick 2 dates from 2 boxes

they are choosing holidays from date1 to date2

that information goes into a holiday table

holiday id
staff id
date
am
pm
comments

to make this work i need to put the date in for each day in the range

so it the person wanted 12/08/07 to 19/08/07

they would have an entry in the table for
12
13
14
15
16
17
18
19
(long dates)

if you can suggest a better way then please do but from this holiday table i will need to do crosstabs and searches to allow

an individuals holiday dates
as well as a crosstab showing how many people are off at any particular time.

thanks again for your help

[Rabbit]

Can you provide an example of the end result you're looking for?
Specifically I'm wondering exactly what you mean by

1) an individuals holiday dates
2) a crosstab showing how many people are off at any particular time

and whether or not you really need the individual dates.

[NeoPa]

Am I right in thinking that you want, at least to start with, a query that returns all the days between two dates regardless of any records currently existing in any tables?
So, if the supplied dates were 19 Nov 2007 and 22 Nov 2007 this should provide a list consisting of :

19 Nov 2007
20 Nov 2007
21 Nov 2007
22 Nov 2007

[Dan2kx]

yes that will do for now, if i can get that then i can stick the information from that into an append query or summet.

ill try and explain the problem again then,

i have one employee table

and a holiday table

one employee can have many holidays obviously so as well as display a list of their own holidays, i want an admin page for supers and for others to check who else is off, the easiest feasible way i can contemplate that is to have a crosstab query count the enties and collate staff members and then count how many off for a date range.

so if i was just to enter holiday info the easy way ie in just two fields in a table (from -to) then the counting crosstab wouldnt work probly as far as i can imagine, i also need to calculate remaining allocations and whatever else you can think of.

hope this helps

and thank you again for your effort

[Rabbit]

I think I see the reason why you need each individual day now. I actually don't think you need to store every day that an employee has requested off. Rather, you should be able to store the Start and End Dates and then use a query to compare it to a table that lists all 366 days of a year (Feb. 29 included for leap year purposes). From that you should be able to crosstab and calculate what you need.

I think this will work fine as long as the Start and End Dates are in the same year... it'll get a bit more complicated if it spans to the next year.

[Dan2kx]

yeah thats the problem it would need to be an indefinate table that way

i think it can be done with a record set

but i can never get the damn things to work

it would go something like this tho

set startdate as ![startdate]
set enddate as ![enddate]

recordset.movefirst

loop until startdate =enddate

thing is i dont know how to code that, (and make it work) and i dont know how to extract the recordset and put it into an action query

[Rabbit]

I don't think you need to do any coding at all, let me test out my theory and i'll get back to you.

[Rabbit]

Ok, I was unable to get it to account for leap years but with more expressions that shouldn't be a problem either.

Create the following table:
tbl_MDCodes
MCode; Number; Byte
DCode; Number; Byte

Run the following code in a module:

Function PopulateTable()
    Dim dt As Date
    dt = #1/1/2007#
 
    DoCmd.SetWarnings False
 
    Do Until dt = #1/1/2008#
        DoCmd.RunSQL "INSERT INTO tbl_MDCodes VALUES (" & Month(dt) & "," & Day(dt) & ")"
        dt = dt + 1
    Loop
 
    DoCmd.SetWarnings True
End Function
 

That will create 365 records, one for each day of a year. You will only have to run it once just to populate the table.

Then, this query will return a record for each day that an employee has requested off:

SELECT tbl_Holidays.EID, tbl_Holidays.StartDate, tbl_Holidays.EndDate, IIf(CDate(MCode & "/" & DCode & "/" & Year(StartDate)) Between StartDate And EndDate,CDate([MCode] & "/" & [DCode] & "/" & Year(StartDate)),CDate([MCode] & "/" & [DCode] & "/" & Year(EndDate))) AS DateOff
FROM tbl_Holidays, tbl_MDCodes
WHERE (((CDate([MCode] & "/" & [DCode] & "/" & Year([StartDate]))) Between [StartDate] And [EndDate])) OR (((CDate([MCode] & "/" & [DCode] & "/" & Year([EndDate]))) Between [StartDate] And [EndDate]))
ORDER BY tbl_Holidays.EID, IIf(CDate(MCode & "/" & DCode & "/" & Year(StartDate)) Between StartDate And EndDate,CDate([MCode] & "/" & [DCode] & "/" & Year(StartDate)),CDate([MCode] & "/" & [DCode] & "/" & Year(EndDate)));
 

If you have a LOT of records, this will be much much faster than individual inserts. Then you can do your crosstabs and counts off this query.

Edit: Actually, I can't think of a viable way to account for the leap year, if this is a problem then you will have to go with VBA code.

[Dan2kx]

OMG that query is complicated lol

i forgot to mention that the boss wants all updates audited, so i will need a new table to say

whos been booked off, when, DT booked, who booked.
as well as cancellations (to be audited)
he also wants it to be calculated in minutes (dont ask me why i think thats mad)

so that is another reason i was goin to do it for each date, just for ease,

im goin to have to have a big think, but i still think it is easier to do it each entry for each day, but i could still use ur vb code (didnt know how to do that)

i could use it (with your permission of course) to create a table with dates (as you have) but instead of specifing the date, define the "date to" box as the end point

-are you following this rant? lol

and then look up that date range from the form and wham bham, hopefully lol

two questions tho, can it be specified the start date in the VB code you posted? just to shorten it?
and
how to delete the table when done to "reset" the field?

thanks again so much for your help
(sorry for the novel)(and hope it makes sense)

[Rabbit]

OMG that query is complicated lol

i forgot to mention that the boss wants all updates audited, so i will need a new table to say

whos been booked off, when, DT booked, who booked.
as well as cancellations (to be audited)
he also wants it to be calculated in minutes (dont ask me why i think thats mad)
The query can return all that. If you want it in under a minute, then the query is the way to go, not VBA code.

so that is another reason i was goin to do it for each date, just for ease,

im goin to have to have a big think, but i still think it is easier to do it each entry for each day, but i could still use ur vb code (didnt know how to do that)
If by "each entry for each day" you mean 3 entries for 1/1/2007 to 1/3/2007, then that's what the query does.

i could use it (with your permission of course) to create a table with dates (as you have) but instead of specifing the date, define the "date to" box as the end point

-are you following this rant? lol

and then look up that date range from the form and wham bham, hopefully lol

two questions tho, can it be specified the start date in the VB code you posted? just to shorten it?
My code was a one time thing to create a static table of Months and Days, it does not need to be changed after being created once.
and
how to delete the table when done to "reset" the field?
This is only necessary if you want to use VBA Code instead

thanks again so much for your help
(sorry for the novel)(and hope it makes sense)

[Dan2kx]

ok well i will pick apart the data fields to match then and let you know if i get it going!!

thanks again for your help

i owe ya

[Dan2kx]

Sorry to trouble you again... im stuck already

how have you set up your table (from the vb)?

when i try it says table not found

so i made a table with "date" as the field but it didnt work

[Rabbit]

Before you run the code you need to create the following table with 2 columns:

tbl_MDCodes
MCode; Number; Byte
DCode; Number; Byte

[NeoPa]

Wow - you guys have been busy since I was last on.
Good going :)

[Dan2kx]

At a guess i would think that the reason it doesnt populate leap years is 'cos there isnt one between 2007 and 2008 (is there??) so for a year with one i would guess that it would fill

ok i have set it up

but i am having trouble contemplating the useage

so i have a staff table...
and the holiday table links staff code with start and end dates
so when to calculate the "minutes" they have left i can count the entries for that person and multiply and whatever

and the same when crosstabing a multi staff bit?

so this query just runs on th fly when i need data

right, now that i have written it down it makes sense just confirm that i am on the correct wavelength

gosh this is th most complicated thing i have had to do ever lol

last week i did a stock control database and that was easy compared to this!!!!

[Dan2kx]

well just knocked up a crosstab and its good

few more things i'd like to do, a sum of the ppl off on each date i can sum by person but it wont let me add a totals row, so ill probly have to do another query to count dates,

and i still have a lot of work to do to make it work probly as i am sure u can imagine

b ut once again i am very grateful for your help, and i still manage to avoid the dreaded record set (YES!!!!)

Dan out...... for now

[Rabbit]

well just knocked up a crosstab and its good

few more things i'd like to do, a sum of the ppl off on each date i can sum by person but it wont let me add a totals row, so ill probly have to do another query to count dates,

and i still have a lot of work to do to make it work probly as i am sure u can imagine

b ut once again i am very grateful for your help, and i still manage to avoid the dreaded record set (YES!!!!)

Dan out...... for now

Yeah, it'd be easiest to do it in another query. You technically can do it all in one query by turning that entire query into a subquery but readability becomes an issue.