Tang
Posts: 14
|
| Posted: 07/19/2004, 10:02 AM |
|
I am trying to setup a system to schedual personnel for deployment, but I need to restrict the amount of certian types of employees per certain dates. The fields invovled are: TITLE_CD(employee type), START_DATE, and END_DATE.
I've written a two working Querys in my Access database that works with exsisting Access forms within the database, but of course I need to use fields/control boxs within the webpages generated by CCS as my filters not access forms. I figured if I could design the idea behind what I needed in Access I just have to figure out a way to implement it into CCS.
Here is the SQL of the 2 working querys in Access:
Query1:
SELECT TBL_DEPLOYED.TITLE_CD, TBL_DEPLOYED.START_DATE, TBL_DEPLOYED.END_DATE
FROM TBL_DEPLOYED
WHERE (((TBL_DEPLOYED.TITLE_CD)=[Forms]![Form1]![TITLE_CD]) AND ((TBL_DEPLOYED.START_DATE)>=[Forms]![Form1]![Start_Date_Text]) AND ((TBL_DEPLOYED.END_DATE)<=[Forms]![Form1]![End_Date_Text]));
Query2:
SELECT Count(Query1.TITLE_CD) AS CountOfTITLE_CD
FROM Query1;
Those two querys output a total for the amount of employees schedualed between those dates, for the given TITLE_CD. If that total is greater or equal to 5 I need to deny access to insert that record.
I am working with a MS Access2000 db. I have a very basic working knowledge of VB/Code writing, but not good enough to come up with a solution to this problem.
I realized I was in way over my head when trying to tackle this problem, so I came here. Thanks in advance,
Tang
|
 |
 |
Tang
Posts: 14
|
| Posted: 07/21/2004, 7:25 AM |
|
If there is anymore information that anyone would need to help you help me, I am closely monitoring the thread.
Thanks again,
Tang
|
|
|
|
Walter Kempees
|
| Posted: 07/22/2004, 2:36 AM |
|
> Those two querys output a total for the amount of employees schedualed
between
> those dates, for the given TITLE_CD. If that total is greater or equal to
5 I
> need to deny access to insert that record.
>
What exactly do you mean with "need to deny access to insert"..."
If you mean that the result of the first query should never show more than
50 records, than try using LIMIT 50.
This will work.
If you mean anything else then pls reply back.
Walter
When building an application for the governement employment agency in the
past we did the same, selecting unemployed for a vacant job.
We did do a random select though to cater for the fact of always selecting
the same first 20 (LIMIT 20) thereby discriminating people with a longer
unemployment period (ic lower idnumber)
"Tang" <Tang@forum.codecharge> schreef in bericht
news:240fbfeb273f05@news.codecharge.com...
> I am trying to setup a system to schedual personnel for deployment, but I
need
> to restrict the amount of certian types of employees per certain dates.
The
> fields invovled are: TITLE_CD(employee type), START_DATE,
and
> END_DATE.
>
> I've written a two working Querys in my Access database that works with
> exsisting Access forms within the database, but of course I need to use
> fields/control boxs within the webpages generated by CCS as my filters not
> access forms. I figured if I could design the idea behind what I needed
in
> Access I just have to figure out a way to implement it into CCS.
>
> Here is the SQL of the 2 working querys in Access:
>
> Query1:
> SELECT TBL_DEPLOYED.TITLE_CD, TBL_DEPLOYED.START_DATE,
TBL_DEPLOYED.END_DATE
> FROM TBL_DEPLOYED
> WHERE (((TBL_DEPLOYED.TITLE_CD)=[Forms]![Form1]![TITLE_CD]) AND
> ((TBL_DEPLOYED.START_DATE)>=[Forms]![Form1]![Start_Date_Text]) AND
> ((TBL_DEPLOYED.END_DATE)<=[Forms]![Form1]![End_Date_Text]));
> Query2:
> SELECT Count(Query1.TITLE_CD) AS CountOfTITLE_CD
> FROM Query1;
>
> Those two querys output a total for the amount of employees schedualed
between
> those dates, for the given TITLE_CD. If that total is greater or equal to
5 I
> need to deny access to insert that record.
>
> I am working with a MS Access2000 db. I have a very basic working
> knowledge of VB/Code writing, but not good enough to come up with a
solution to
> this problem.
>
> I realized I was in way over my head when trying to tackle this problem,
so I
> came here. Thanks in advance,
> Tang
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Tang
Posts: 14
|
| Posted: 07/22/2004, 9:52 AM |
|
Quote :What exactly do you mean with "need to deny access to insert"..."
Hello Walter,
Sorry I was confusing in my explanation, trust me I'm confused myself.
I need to deny those personnel schedualing themselves for that date if there are already too many, depending on thier TITLE_CD(rank).
Those querys I designed above only work within Access2000. What I need are querys within the code of the webpage that run when they hit the "ADD" button. Those querys within the webpage would then allow or deny based upon the parameters I stated above.
The problem I'm having is that I really don't undersatnd how to use, or run querys, the validate on submit function built into CodeCharge Studio. Or how to implement querys based upon text boxes in the webpage.
Tang
|
|
|
|
Walter Kempees
|
| Posted: 07/24/2004, 1:37 PM |
|
From your text I understand you are using MsAccess VB deploying CCS.
Now I have no experience with this combination but is should actualy no
problem I think.
As I get it from your text, a user sees a form, the user is already logged
in and therefore you know his TITLE_CD.
They can choose to apply for a job in a certain period and register
themselves for that job as long as that period is not filled (max entries
reached).
Right?
Ok well you state that you can do it in Acces, so it should be easier to do
it in CCS.
Assuming you have at least read the tutorial/Getting started (worth it!).
Have you done anything in CCS yet on this project?
The application builder wizard as well as the other available wizzards give
the opportunity to create basic grid and record forms, your approach would
be to built something that approaches your goal without being complete and
then after that refining the sql statements to your requirements.
It is difficult for me to write a complete solution, but if you supply me
with your tables for this part of your problem, I would be happy to build
you a small prototype from which you can get on.
Let me know.
Your SQL statement are not scaring, the conform to what can be done in CCS,
of courese your form.form lingo is different but then....
Hope I inspired you to go ahaed and do it.
Otherwise, come back.
"Tang" <Tang@forum.codecharge> schreef in bericht
news:240fff0dc92d8c@news.codecharge.com...
> Quote :What exactly do you mean with "need to deny access to
> insert"..."
>
> Hello Walter,
> Sorry I was confusing in my explanation, trust me I'm confused myself.
>
> I need to deny those personnel schedualing themselves for that date if
there
> are already too many, depending on thier TITLE_CD(rank).
>
> Those querys I designed above only work within Access2000. What I need
are
> querys within the code of the webpage that run when they hit the "ADD"
button.
> Those querys within the webpage would then allow or deny based upon the
> parameters I stated above.
>
> The problem I'm having is that I really don't undersatnd how to use, or
run
> querys, the validate on submit function built into CodeCharge Studio. Or
how
> to implement querys based upon text boxes in the webpage.
>
> Tang
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Tang
Posts: 14
|
| Posted: 07/28/2004, 9:40 AM |
|
Thanks again walter sorry for the slow reply.
Actually the person schedualing is schedualing for thier whole unit, so the logged in person is not the person being schedualed. But obtaining the TITLE_CD for the person to be schedualed is one of the few things I am able to do atm.
Ya did go back and re-read the User Guide and example .pdf manuels, and found a few interesting ideas, but nothing that really gave me complete direction. I guess the real problem I'm having is how to write the querys within the "On Submit" event of the add or update button.
I was thinking of setting up a varible for the problem. If varibleX is greater than 5 then do not allow update/add. The varible would be the direct output of the querys designed to find out how many people are schedualed for that date range. But again, doing so is proving hard to do than anticipated.
Thanks again,
Tang
|
|
|
|
|
