Related Consultants And Assignments Based On Dates
Mar 26, 2007
In my database I have:
* A table of consultants
* A table of contracts
* A table of assignments (shows which consultants are assigned to which contracts, along with the start and end dates of those assignments)
* A table in which consultants book their time to projects
The consultant assignments change over time. What I need to do is create a query that shows a) the amount of time a consultant booked to a project(s), and b) the contract to which he/she was associated at the time. For example, if a consultant was associated to Contract A in February, Contract B in March, and Contract C in April, I want to be able to accurately reflect that in my query. I'm not sure how to go about doing this in my query.
I'm sure many here have solved similar problems, so any help you can give will be appreciated.
I have a table that lists classes available (tblCourses); one that lists class dates (tblDates) and another that lists employees (tblEmployees). I have a fourth table that combines that data into classes taken by the employee (tblTraining). I have a form to input the employee's training with combobox fields for the COURSETITLE and CLASSDATE. I want the CLASSDATE field to only display the dates available for the specific class that was selected. How do I do that?
I am developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.
With this table structure there is a many-to-many relationship between Claims and Events, but, there will only be one of each event per claim. Is there a better way to set up the tables to enforce a 1-to-1 relationship?
I was planning on creating a form for the Claim table with a subform for the Events joined on ClaimID and fkClaimID, but that would allow users to create more than one set of Events per claim, if that makes sense.
Help please. I have a database with patient authorizations to various providers. They may have more than one authorization on the same day. I need to select the patient and the most recently assigned provider (other selection criteria will also be used). Data may look something like: Authno Authdate Membno Provnam 1 20050201 100 AMH 2 20050402 200 Seton 3 20050404 100 Seton 4 20050404 100 Seton 5 20050501 200 AMH
How can I create a report to project out 6 quarters what qualified Job assignments will still be available :confused: The qualification code will be Q for qualified people.
I was wondering how to create a form which is using data from three tables, the data in the tables does not have any relationship setup as they are not related to each other. When I use the wizard and I select the three tables, Access starts complaining about the fact that no relationship has been setup...
A have a table of volunteers and a table of assignments. I can run a query/report of volunteers who are assigned. How do I create a query of who is not assigned.
Table1 has fields docno (text) title (text) progress (number)
sample data is like below:
doc-001 test1 90% doc-002 test2 25%
Table2 has fields
sample data is like below:
id (number) icon(attachment type)
1 image1 2 image2
Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2
I have 2 tables, one is like a main table, containing all of the main data, such as a Job Number, Customer, Quantity, etc. I have a second, related, table that acts a breakdown of information. There may be several related records to one main record, it entirely depends on the nature of the job.
What I'd like to do is run a function that looks at a main record, checks if all the related records COMPLETED field is ticked and then tick a field in the main record. I only want it to do this for records where all of the related records are COMPLETED.
In my database I have a table that keeps track of a package of items. The package is assigned a package type (counter display, end cap, half pallet, full pallet for example). The record of the association of the package and it's type is held in the main table.
Each of these package types is either a case or a pallet (counter display and end cap are cases and half and full pallets are pallets) This relationship is kept in another table (we will call it description table).
Now, based on the type of package and therefore it being a case or pallet a UPC and a GTIN number are assigned. The GTIN number is different if it is a case or is a pallet. I have a table that stores all of the UPC and GTIN numbers available in 3 columns, one for UPC, one for GTIN Case and one for GTIN Pallet (the UPC is a standard 12 digit and the GTINs are 14 digit -with the first 2 different to designate pallet or case. and all are based on the check digit formula necessary)
The user assigns the UPC and correct GTIN number by clicking a button which applies the next available UPC code to the package and determines if the description of the type (case or pallet) and inserts correct GTIN number into that field. I actually have all of this functioning correctly.
Now the problem. If a user changes the package type, and therefore changes the description, I need to add code to the update event of the combo box that gives the choices for package type that does the following:
Check to see if the original package type was a case or pallet (it's description) and if by changing the package type it is now changed to the other, update the record in the main table to the correct GTIN number based on the existing UPC Code.
OR as I write this, maybe the code could simply update the main table with the correct GTIN code based on the new description and the existing UPC code. This was I would not need to check for a change just do the update every time.
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:
Categories Table: CategoryID (PK) Description
Users Table: UserID (PK) Username CategoryID (FK on Categories.CategoryID)
Jobs Table: JobID (PK) CategoryID (FK on Categories.CategoryID)
JobDetails Table: JobID (FK on Jobs.JobID) UserID (FK on Users.UserID)
Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:
SELECT Users.ID, Users.Username, Users.CategoryID FROM Users, Jobs WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));
All the tables are linked with relationships but my SQL isnt so hot! Any ideas as to how i would do this and get it working?
Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).
I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.
How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.
For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.
I'm creating a db that provides project listings in the work queue, along with the due dates and a bunch of other data.
I have created a table [recurring projects] that stores the projects that are done frequently. Once I do some field manipulations on this table, I append them to a master table with all projects [projects] - ad hocs, non-recurring, etc.
Most, not all, recurring projects are due 14 days from the date of assignment, however it varies. I've created a query that populates the due date 14 days or whatever the user has inputed for working days from the assignment date.
All works good with this functionality.
Here's my problem:
There are some projects that are due on the 1st, 15th or some designated date each month. Typically the projects are assigned a due date prior to the month due (i.e. February projects are assigned in January). Since the due dates change each month, is there a way to code a query to look for the first process - 14 days - and if it is null, then populate with a day of the month due.
For example, the field [days_to_complete] = 14 so the query will populate 14 days from 1/31/07 resulting in [due_date] = 2/14/07 OR [days_to_complete] is null, but [day_of_month] = 1 , which I need to create the [due_date] = 2/1/07
I'm racking my brain and pencils are being cracked!
I would like a query to return dates based upon the input of just the day and month. At the moment I have a parameter query which asks for 'start date' and 'end date' and this works fine, but I want the query to return all the records for all the years in the database and not just the current one (date format is dd/mm/yy)
So if I type <start date> 01/01 and <end date> 02/01 the query will return:
Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ?
For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually.
So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example).
- I currently have a table that is labeled WorkSchedule. I then Created a query called WorkSchedule Query.
- The column labels are as follows (listed from right to left): Project, Event (something inside of the project), Employee1, Employee2, StartDate, EndDate
- I created a split form that shows each record.
- On each record I made the employee1, and employee2 as a drop down box that allows you to pick from all employees.
*MY QUESTION*
How do I make a pop up form appear when there is a conflicting date based on the employee I picked?
For example: If I had assigned Rebecca to an event that went on from 11/12/2014 - 11/14/2014. Then I tried to assign her to another event from 11/13/2104-11/15/2014. I want a pop up message saying that these dates conflict.
I have a list of class sessions. To help with maintaining attendance, each session has its own roster. I just need to add a functionality so user can make sure the attendance roster is synchronized with the past sessions and future sessions in events of last minute additions/drop or updating names/contact infos. The sessions are related to themselves as below:
ClassID PreviousClassID 1 Null 2 1 3 2 4 Null 5 4
So therefore to propagate the changes into all sessions how would I instruct the query to look for each related ID then checks if it's also related...
I'm thinking I may have to use VBA to do what, but I hope that can be done with a append query.
Any ideas on calculating future dates based on # of working hours. For example... If a task is suppossed to be completed in 32 business hours, when would that be? Considerations include standard working hours M-F, no working hours on weekend. I have to believe that someone has written this before and I don't need to reinvent the wheel. ANy help is much appreciated!
I have a query that has some reminder dates. 10 days before an event and 5 days after. I was wondering if it would be feasible to create an outlook reminder based on the query and if so how would I go about it?
I am trying to create an attendance database, our company introducing the point system attendance,
Called Off (CO) 2 points Left Early (LE) 1 Point Tardy (Tar) 1 point
Employee can reduce point if they have perfect attendance for 90 days from the last day of violation. For example, an employee absent on 01/01/2015, he will received 2 points, the credit will giving on 04/01/2015, if there is no violation, but if he absent again on 03/31/2015 not only he will received 2 more points his 90 days will start from 03/31/2015, now he will eligible to get credit on 06/29/2015 and so on.So far I have created 3 tables and 1 query.
Tables
Employees: Id, Last Name, First Name Points: Id, Description Points Attendance:Id, Date, Employee ID, Points ID
Query Date Employee ID Last Name First Name Point Description Points
how and which formula to use which calculate the points based on above example.
I've been trying to solve this problem for the past month and at the verge of destroying my PC! I've trawled through numerous web sites but just cant seem to figure this one out.I'm trying to get access to tell me what the next service moth is based on the start date, end date, and the frequency of service.
For example:
Start date: 01/01/2014 End date: 31/12/2015
service frequency: Every 4 months
Using the above information the service months are: 1 - April 2014 2 - August 2014 3 - December 2014 4 - April 2015 5 - August 2015 6 - December 2015
Code: tblPrice PosNr PriceDate Company Price 1 01.01.2014 Firma A 5 2 02.01.2014 Firma A 7 3 03.01.2014 Firma A 9 4 04.01.2014 Firma A 8 5 06.01.2014 Firma A 6 6 02.01.2014 Firma XY 11 7 03.01.2014 Firma XY 9 8 04.01.2014 Firma XY 7 9 05.01.2014 Firma XY 8 10 06.01.2014 Firma XY 10
And I have a table with the dates, for which I need a price.
Code: tblDates PosNr PriceDate Company 1 01.01.2014 Firma A 2 02.01.2014 Firma A 3 03.01.2014 Firma A 4 04.01.2014 Firma A 5 05.01.2014 Firma A (no price available) 6 06.01.2014 Firma A 7 02.01.2014 Firma XY 8 03.01.2014 Firma XY 9 04.01.2014 Firma XY 10 05.01.2014 Firma XY 11 06.01.2014 Firma XY
And now I want to combine this tables, and for the dates which have no price, the last price should be taken.
Code: tblResult PosNr PriceDate Company Price 1 01.01.2014 Firma A 5 2 02.01.2014 Firma A 7 3 03.01.2014 Firma A 9 4 04.01.2014 Firma A 8 5 05.01.2014 Firma A 8 (actualy no priceavailable, so take last price) 6 06.01.2014 Firma A 6 7 02.01.2014 Firma XY 11 8 03.01.2014 Firma XY 9 9 04.01.2014 Firma XY 7 10 05.01.2014 Firma XY 8 11 06.01.2014 Firma XY 10
how I can get this?
I have this code, but it need hours.
Code: SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1 B.Price FROM tblPrices As B WHERE B.Company = tblDates.Company AND B.PriceDate <= tblDates.PriceDate ORDER BY B.PriceDates DESC ) AS Price FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);
I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber
I also have a table called FCDateRange with three fields
DateStart DateEnd and DotNumber
For example
3/7/13 - 3/13/13 - 1 3/14/13 - 3/20/13 - 2
Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.
Here is what I have:
Code: Sub Update_Dates() Dim rs As Recordset Dim db As Database Dim lmsg As String Dim transactiondate As Date Dim strsql As String
I am trying to filter a report based on two user inputted dates, but can't seem to figure it out. I've played around with quotation marks, and # but can't seem to figure the syntax out.
Code: Me.OrderBy = "Date Submitted" Me.OrderByOn = True Dim Date1 As Date Dim Date2 As Date Date1 = UserInput Date2 = UserInput DoCmd.ApplyFilter WhereCondition:="[Date Submitted] > Date1 and < Date2"
