I am needing a database that will help to manage employee leave (holiday) requests. I have one but the current design is a bit...... well, you know.
Leave Limits are (presently) applied by one table:-
tblLimit
-------
payWeek
workType (twentysix work types some common to all sites, some apply only to one or two sites)
LimitSiteA (three sites)
LimitSiteB
LimitSiteC
LimitAllSites
So, several "faults" are immediately apparent:
LimitAllSites is ALWAYS the sum of LimitSiteA+LimitSiteB+LimitSiteC so
clearly shouldn't be there, and I don't even want to talk about that....
Also , no primary key, repeating columns... uh-oh!
Therfore....
tblLimit
-------
limitID (PK)
payWeek (date/time - always a Wednesday, our payWeek begins on a Wednesday)
workTypeID (FK to new table tblWorkType)
siteID (FK to new table tblSite)
Limit (integer)
However....with just a brief investigation, it transpires that of the 1600+ rows in this (current version) table, there are only 69 unique sets of limits (ran a Unique Values query, excluding the PayWeek).
So, what I do want to talk about is not having to add in a table row for each WorkType for each PayWeek (for each site- with the improved design). I want to apply a general set of limits, with effectiveFrom and effectiveTo dates, per Site/workType combination, which should automatically populate out 80 weeks into the future (historical records are not required - it is just to help plan future leave) and then cater for the exceptions by "adjusting" the general limits in one of two ways:
1) every day over a date range (e.g. every day between 15NOV05 and 15DEC05, decrease the limit for worktype Z in Site A by 10 percent of the BASE* limit).
2) every nominated weekday over a date range (e.g. every Friday in December 2005, increase the limit for worktype Z in Site A by 20 percent of the BASE limit).
*the two adjustment types need to be independent, overlappable, and have a cumulative effect.
e.g. for the above, if the general limit for type Z of site A were 100
14 NOV --> limit 100 = 100 (no adjustment)
15 NOV --> limit 100-10 = 90 (adjustment per #1)
.
. - repeat concept as above
.
30 NOV --> limit 100-10 = 90 (adjustment per #1)
01 DEC --> limit 100-10 = 90 (adjustment per #1)
02 DEC --> (Friday) --> limit 100-10+20 = 110 (adjustment per #1 & #2)
03 DEC --> limit 100-10 = 90 (adjustment per #1)
.
. - repeat concept as above
.
15 DEC --> limit 100-10 = 90 (adjustment per #1)
16 DEC --> (Friday) --> limit 100+20 = 120 (adjustment per #2)
17 DEC --> limit 100 = 100 (no adjustment)
etc
It gets dirtier !
There are two broad types of leave - general - for which the limit is determined/applied at the (site+workType) level, and "long service" for which the limit is to be applied at just the site level (workType has no bearing).
I was OK up until it got "dirtier" - now I am not so sure....
I was thinking...
tblLimit (base limits)
-------
limitID (PK)
workTypeID (FK to new table tblWorkType)
siteID (FK to new table tblSite)
limit (integer)
effectiveFrom (date/time)
effectiveTo (date/time)
Does my base limit table store WorkTypeID, but as null when the record relates to "long service" leave, and as a valid WorkTypeID when the record relates to "general" leave ? If so, is it of sound design to derive the broad leave type from the "nullness" of the workTypeID, or is it recommended to store that separately?
Similar questions relate to the adjustment table, for both WorkTypeID and WeekdayID...
Hello! i have created three tables and inserted different records in them. I want only "genes" records from table 1,2 and 3 to be shown. The problem I am having is that when I design the query and ran it, it shows me all the possible combinations of records instead of showing only 1 record.
Table 1 - Number - Population - Genes Table 2 - Number - Genes Table 3 - Number - Genes
Table 1 - Records ============== Number 1 - India - AB48 Number 1 - Pakistan - AB35 Number 1 - South korea - AB48
Table 2 - Records ============= Number 1 - BD34 Number 1 - GF45
Table 3 - Records ============= Number 1 - HG65 Number 1 - LK98
SQL ===== SELECT Table1.Number, Table1.Population, Table1.genes AS Table1_genes, Table2.genes AS Table2_genes, Table3.genes AS Table3_genes FROM Table3, Table2, Table1;
Now the question is that either I need to use Criteria or Filter to show the each record from the table. The problem I am having is that when I run this query it shows me all the possible combinations of each record from the table.
I want to show only the records which are in those fields and nothing more or less.
Could you please help.
SIZE="1"][Please note: The database I have shown here is an example and does not reflect my personal database however I have made an example to show what i am trying to achieve here][/SIZE]
I have been working on a Query to sort through a list of companies. I am trying to query the company name to include ALL entries that have the include key(from another table) anywhere in their company name... It is supposed to exclude all entries with ONLY the exclude key(from another table)... If it has the exclude key AND the include key or neither key then the entry should be included... Then it is supposed to use all of the entries added from the previous sorts and include ONLY the entries where the zipcode matches one from a ZIPCode list.
Below I have listed the tables with their variables and my attempt on the query:
Include Table (tblinc) ID Inckey
Exclude Table (tblex) ID exkey
List Table (list) ID Company Address City State ZIPCode …
ZIPCode Table (ZIPCode) ID ZIPCode
Query withZips <--- my attempt... doesnt work 100% ALOT OF REPETITIVE ENTRIES
SELECT DISTINCT tblinc.inckey, tblex.exkey, ZIPCode.ZIPCode, * FROM ((list LEFT JOIN tblinc ON list.company Like "* " & tblinc.inckey & " *") LEFT JOIN tblex ON list.company Like "* " & tblex.exkey & " *") INNER JOIN ZIPCode ON list.ZipCode Like "*" & ZIPCode.ZIPCode & "*" ORDER BY inckey DESC , exkey;
This has become urgent... If you can help, please respond asap...
I have a normal schedule table in my database that stores the open/close hours for each business unit location.
The problem is some locations are timeshares and some have different business units every other week, every 2 Tuesday, the 1st and 3rd weeks of the month.
I figured I would make an exception table noting all of these somehow and after building a calendar/report plug in the exceptions.
Is there any way to force a field value to be unique and of a set length, but with exceptions?
Let me explain... I have a text field in my table called "employee_number" and this value is always one of the following:
NULL an 8-digit number the word "External"
What I want to do is to force that field to either be NULL, the word "External", or a unique 8-digit number.
Is this possible? Obviously I can't set the source field in SQL to accept unique values only but I wondered if there was any way around it at form level?
I have been working on a Query to sort through a list of companies. I am trying to query the company name to include ALL entries that have the include key(from another table) anywhere in their company name... It is supposed to exclude all entries with ONLY the exclude key(from another table)... If it has the exclude key AND the include key or neither key then the entry should be included... Then it is supposed to use all of the entries added from the previous sorts and include ONLY the entries where the zipcode matches one from a ZIPCode list.
Below I have listed the tables with their variables and my attempt on the query:
Include Table (tblinc) ID Inckey
Exclude Table (tblex) ID exkey
List Table (list) ID Company Address City State ZIPCode …
ZIPCode Table (ZIPCode) ID ZIPCode
Query withZips <--- my attempt... doesnt work 100% ALOT OF REPETITIVE ENTRIES
SELECT DISTINCT tblinc.inckey, tblex.exkey, ZIPCode.ZIPCode, * FROM ((list LEFT JOIN tblinc ON list.company Like "* " & tblinc.inckey & " *") LEFT JOIN tblex ON list.company Like "* " & tblex.exkey & " *") INNER JOIN ZIPCode ON list.ZipCode Like "*" & ZIPCode.ZIPCode & "*" ORDER BY inckey DESC , exkey;
This has become urgent... If you can help, please respond asap...
tblTimeEntry records individual time slips for attorneys keeping track of their time. Each slip records the client, attorney, time, rate, and value (time * rate) of the slip.tblPeople holds all the timekeepers and their current rate. Their rates change once a year.tblCustomRate will hold the exceptions,
How I imagine tblCustomRate will be set up is as follows (and this may be how it's wrong):Columns for each timekeeper. Each record is a client entry. When a new time entry gets entered, as it gets saved into tblTimeEntry, it should check to see if the client number is one that has a record on tblCustomRate. If it does, it should find the column for the timekeeper and use that rate. could add columns to the tblPeople for each client that gets a custom rate and use IF statements to get it to add, but that's a lot of legwork and code for a few clients.
tblTimeEntry TimeID (primary key) ClientID TKFN (Timekeeper First Name) Rate Value
If it matters, this is Access front end with SQL Express tables. It's few and far between that I set up new People and it would be few and far between that there would be any custom rates (currently, there would be 2 out of 2000). People's rates change, but the time slips they entered at their old rate should stay at their old rate. If they were only worth $100/hour in 2010, but now they're worth $200/hour, the 2010 slips stay at the $100/hour rate.
If my make queries in the data base and the source data base is another .mdb and the table names in the other .mdb which would be used for the queries are the same as those in the data base where the queries would be made......does anyone see any problems with that in the area of corruption or similar.
The queries made would be indentical to their counterparts in the data base where they are made and would serve the same purpose.
It would be a toggle type of thing whereby the recordsources for the forms in question would be changed.
For what I want to do it works perfectly but I am not sure if there would be problems that would only surface with longer term use and varied conditions as opposed to some short term testing.
I have data from a survey with qualitative responses. For a single qualitative question, I moved the ID & responses into a new table and categorized the response according to a bucket/theme, where each column is a new bucket. I now have 10 columns. Each response is represented in 1 or more columns. I used an excel formula to copy the response data into the column itself.
Example:
A1 // B1// C1 // D1// E1//... L1 ID // Response // Cats // Dogs // Elephants //.... Column 10 1 // I like cats // I like cats //(null)//(null)// ... (null)// 2 // I like cats and dogs // I like cats and dogs // I like cats and dogs //(null)//..// 3 // etc.
However, now I'm realizing that Access always wants to show data for all records, or at most I can limit using a WHERE clause in my query.I want to use Access to generate this report:
1. Section 1: Show all responses from the Cats bucket where there is data 2. Section 2: Show all responses from the Dogs bucket where there is data 3. and so on
I know how to do summary values, and I know how to do filtering that apply across the whole report, but this seems like more advanced filtering, where I want to see selective details differently for each field.
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
i would like in a form for a combo box to be able to select an item from a table and input relating information automatically into other boxes in the form..
I have 3 tables: Table 1 has product code and product description. Table 2 has invoice number company details, address etc. Table 3 has product code and product description qty and invoice number.. Table 3 relates to table 2 by the invoice number and table 3 product code looks up the product codes available in table 1 and also table 3 looks up the list of products descriptions in table 1 using the combo wizard. This means the wrong code can be put with wrong description. What i would like to know is how i select a product description and the product code in the form fills out automatically?? i hope this makes sense please helppppp!!
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.
The following formulas will be used in the select statment:
Total Batches = count(BatchNo) for date selected Total Envelopes=sum(Envelopes) for date selected Total Documents=sum(Cases) for date selected Total Pages=sum(Pages) for date selected
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
I have a database that I import data from an excel spreadsheet into multiple times daily. The table that this data is imported into has several key fields that if the data already exisits in the table, and I attempt to import data that is the same except for one or more of the key fields is different. At this time the database it creates a different record. I am trying to get the database to overwrite the data in the database.
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded 121/2/201310:00:00 AM3:00:00 PM 221/3/20132:00:00 AM11:00:00 AM 321/4/201312:15:00 AM11:30:00 AM 421/5/201310:25:00 PM11:00:00 AM 531/2/201311:00:00 AM3:30:00 PM 631/3/201312:00:00 PM10:00:00 PM 731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
Background I have a query (Q1) that retrives data from a table (Table 1). One of the fields in Table (F1) contains both text and numeric data (ie: 24 eggs). I want to separate these values in Q1.
Questions How can i in Q1 retrive only numeric data from F1 and display that data i a field? How can i in Q1 retrive only text from F1 and display that data i a field?
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code: Private Sub Test_Click() Dim strSQL As String
