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
24 2554
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.
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
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".
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 32,349
Expert Mod 16PB
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
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;
-
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
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 32,349
Expert Mod 16PB
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
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
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.
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
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.
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.
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)
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)
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
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
Before you run the code you need to create the following table with 2 columns: tbl_MDCodes
MCode; Number; Byte
DCode; Number; Byte
NeoPa 32,349
Expert Mod 16PB
Wow - you guys have been busy since I was last on.
Good going :)
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!!!!
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
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.
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
2 posts
views
Thread by Liang |
last post: by
|
10 posts
views
Thread by ST |
last post: by
|
3 posts
views
Thread by Dave |
last post: by
|
6 posts
views
Thread by Cecil |
last post: by
|
1 post
views
Thread by Andy L |
last post: by
| | |
5 posts
views
Thread by Chris Botha |
last post: by
| | | | | | | | | | | |