Forms :: Make Multiple Records Based On Occurrence
Dec 13, 2013
I am making a database that has to do with Preventive maintenance. Everything is going good and I came to a problem. There are recurring events. Some are weekly monthly quarterly etc all the way to 5 years. When I put in the task and a start date and the occurrence. So Say I put in a date of a task for today and its set to monthly I want the next record to be 1/13/14 for the task and go all the way up to infinity until a box is checked making it inactive.
View Replies
ADVERTISEMENT
Mar 8, 2008
As my screen name implies, I am a new to Access and I’m seeking some input to resolve an issue. I have tried numerous queries, read MSDN forums and Access help w/o success. I hope someone would have some ideas.
See ATTACHMENT for screen shot of the following scenario:
Two tables in a one-to-many relationship. The parent table (tblAdmByWard) contains the autonumber primary key (PatientID) and additional fields that will not change while the child table (tblContStayEval) has a corresponding field (i.e. PatientID) to secure referential integrity PLUS additional fields as shown that are evaluated and updated daily. During the update the current date is entered (DateOfReview) along with additional info thus creating a new daily record in the child table.
I would like to query the child table (tblContStayEval) to find and return ONLY the first occurrence (DateOfReview) that the criteria (Variance) was selected…i.e.2/24/2008 as illustrated in the screen shot. This needs to occur for all (PatientID) records but exclude any subsequent and/or similar selections. By “similar” I mean the date will be different for each record in the child table while the data in the other associated fields may or may not be.
I hope I explained this okay as I’m not sure how to go about this. Any ideas or thoughts would be appreciated.
View 2 Replies
View Related
Feb 10, 2015
Currently we track areas of non-conformance for a fleet of flight simulators. Each flight simulator has a particular ID number. In some instance an area of non-conformance is associated with a single simulator, at other times it is a fleet wide issue and applies to all or some simulators. In order to track as well as advise leadership and the contractor responsible for maintenance of the simulators of the situation we generate individual response letters.
In order to track each instance of non-conformance my idea was to create a new record for each deficiency. In the event that it is applicable to multiple simulators I would like to fill out the form with all pertinent data and then place a checkbox associated with each simulator and when the record is saved, it creates one record for each simulator with a checkbox ticked.
Once the deficiency on each simulator is fixed, I would check a box for a field called rescinded, which would remove that particular deficiency on that particular simulator from the active list of deficiencies but the others would still remain because they are associated with unique records.
View 14 Replies
View Related
Feb 19, 2013
Let's say I have a table called "Courses" which among other things has "Completed"(Yes/No) and "Category"(Text).
Now, there also another table "Progress" which has similar columns.
I want Progress.Completed to reflect if all courses of the specified category have been completed.
How would I do that? I'm new to access ...
View 1 Replies
View Related
Nov 28, 2007
Hello people,
I have for some time now been fooling around with a database to keep track of several clinical trials
Basicallly I have made 4 tables..2 which defines the project ( Project and visist), 1 that populates the projects ( patients) and 1 that bind the patients visits to specific dates ( appointment)
The trouble is that im Lazy :D and therfore dont want to enter the appointment data for all patients one at a time each time we get a new guniea pig since all thats diffferent is the dates he/she has to show up.
Im hoping for some way to fill up my appointment table based on patientID and projectID alone.
SO this is my 4 tables... ( some unimportant fields left out)
Project
ProjectID (PK)
ProjectName
ProjectDescription
Patients
ProjectID(FK)
PatientID(PK)
PatientName
patientScrnr
Visits
ProjectID (FK)
VisitID(FK)
VisitName
VisitPrice
DaysToNextVisit ( integer)
Appointments
VisitID(FK)
PatientID(FK)
AppointmentDate
AppointmentID ( PK)
The idea is to create a project and then add a set amount of visits to the project ( days when they have to come and get a blood sample taken)
All patients involved in the project is likewise added in the Patients table and joined to a project.
Since the patients all have to come to a fixed amount of visits ( determined by the project) I was thinking I could make a form where I pick a patient from a combobox
(select projectID,PatientID,PatientName) and then automatically generate a record for each visit in my Visit table where Visits.ProjectID = cboPickPatient.ProjectID
and show them in a subform so I can enter the date they have to come. I hope that makes sense. ( preferably I would like to add the date also by just entering the first Visit date and the using the DaysToNextVisit to make the other visit dates or each patient, but thats a whole other problem :o )
My instinct tells me I have to do some sort of loop code but I havent figured out the details.
Can any of you guys lead me in the right direction before I loose all my hair in frustation??
This seems to me to be a pretty simple problem-making a recordset based on a projectID and the visits involved in that project but im just out of ideas:confused:
Kind Regards,
Brian Bj
View 5 Replies
View Related
Jan 4, 2014
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies
View Related
Jun 21, 2013
I have a form for inserting invoices, and on the subform records I have a command button to print the labels, the label would contain the quantity of the product, so, if the quantity is ex. 11000 and package contains 2000 only.
so I have to print 5 labels with quantity 2000 and one label with 1000 qty
what I need to do, is when I click the label cmd button to insert the 6 records required to print the labels to temp table
View 8 Replies
View Related
May 11, 2013
I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.
View 1 Replies
View Related
Feb 15, 2014
I have created simple data base containing 2 tables connected each other and I wanted to make a form with fields taken from those tables and unfortunately it did not work and I got errors message.
Before I start to build the form do I need to create the query based on those tables and having the fields I need for the form? If yes, what sort of query, simplest one?
View 6 Replies
View Related
Nov 4, 2014
I have a text box linked back to a table and I am needing to be able to make multiple entries at one time for this text box but when I enter the first number and hit tab all the other text boxes that are linked to this one in the table show the same number....
View 2 Replies
View Related
Jan 20, 2015
My problem is the following: when I receive say 5 computers in a purchase form, I want to register the serial number of each of them in another form, bound to another table.If I receive 2 units in the purchase form, my user should only be able to input 2 records in the serial form (a continuous form), if we receive 3, then only 3 records,I cannot quite figure out how to build this second (serial number registration form), so that it refuses input after the correct number has been reached.
View 14 Replies
View Related
Jul 10, 2007
hi,
i am querying a single table that contains card numbers and corresponding transaction codes (amongst other things). a single card number can have multiple transaction codes.
i want to select all card numbers that have both 0100 and 0802 transaction codes.
if i use a select query with ="0100" Or "0802"as the criteria, it returns all card numbers that have either 0100 or 0802 transaction types. if i then change the query to crosstab and group by card number, it is clear that some cards have both transaction types.
when i try to change the criteria to ="0100" And "0802" it returns nothing at all.
i'm sure the solution is something really simple - any idea what i'm doing wrong?
View 7 Replies
View Related
Apr 25, 2014
I am using Access 2013, I have a recipe project with multiple one-to-many relationships. The main table in all of them is RECIPE. Child tables are HOLIDAY, SPEC_NEED, COURSE... each one of these child tables are comprised of multiple checkbox columns (yes or no)...
I need to filter the RECIPE records based on the selected checkboxes in these child tables..so for example if COURSE.Appetizer is checked and HOLIDAY.Christmas is checked RECIPE will produce the appropriate records. How to pull this one off...I am currently using the Options Group design tool and have used a separate Options Group design for each of the child tables...
View 9 Replies
View Related
Mar 3, 2015
I am creating a simple inventory db. I have a form that we enter into the table inventory. Right now we enter in everything once as inventory, then again to out to coating or ship to customer. I’m looking for the code to create a button enter the record twice but make two fields change.
So in short terms I want the form to enter a record with all the data as displayed. Then create another record with all the same data but the quantity would need to change to a negative and the Status would change to “Coating”.
I know this code won't work but this is what I want to do.
Private Sub Command78_Click()
.AddNew
!Employee = Me!Combo68
!Material = Me!Combo48
!Length = Me!Combo29
!Caliber = Me!Combo31
[code].....
View 9 Replies
View Related
Jan 9, 2014
Access 2003
Trying to filter a form based on a field with wildcard. My form has a txtCustFilter control where a customer's name can be entered in part or whole. The Customer's name is in PCCustomerName
This code works but, I'd like to make it case insensitive
Dim strFilter As String
strFilter = "[PCCUSTOMERNAME] LIKE ""*" & Me.txtCustFilter & "*"""
Me.Filter = strFilter
Me.FilterOn = True
View 9 Replies
View Related
Jun 28, 2006
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.
Any suggestions or assistance would be appreciated.
Thankyou
View 1 Replies
View Related
Nov 15, 2005
how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.
View 2 Replies
View Related
Sep 27, 2005
Table1 gives Case Nu.
Table2 gives Case Nu. and EventType.
Table3 gives EventType and Cleared (yes/no field)
I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.
Any ideas? I know there must be an obvious solution, but my mind is stuck right now!
View 2 Replies
View Related
Sep 21, 2015
I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
View 1 Replies
View Related
Jan 3, 2013
I have a table of data regarding companies (contact info, etc). The company I work for provides these companies with up to 10 different products. On my input form, I have created 10 check boxes (and thus, 10 columns of Yes/No data in the corresponding table).
Each company has at least 1 product checked off, and up to all 10.
I would like to create a combo box that lists all 10 products, and upon selecting one, a list box then populates with the names of the companies (primary key) that use that specific product.
So, for example,
Company A buys CATS and DOGS from us
Company B buys DOGS
Company C buys CATS and ELEPHANTS
Combo box options: CATS, DOGS, ELEPHANTS
If I choose DOGS, then a list box gets populated with:
Company A
Company B
and when I click either of these, the record should be brought up for the respective company.
All of this should take place in the header of the form, while the form itself can be updated based on search selections.
View 3 Replies
View Related
Nov 5, 2014
I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.
Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.
I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.
So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".
So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...
Got relationships between:
"contact name" fields in both "table2" and "table3"
"project number" fields in both "table1" and table2"
In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?
Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????
View 6 Replies
View Related
Jul 17, 2013
[URL]
My problem is when i use this form as a sub form, it makes the main form close as well. How can I make the main form stay and only the sub form close.
View 6 Replies
View Related
Sep 24, 2014
I have a problem in my Database system. The title of my Database is Employee Evaluation. This is my problem.. I already make the default value the txtbox a current system date. but for the next day.. the txtbox for all record should become a current system date. but only the for the new record is the current system date. the all records are not current system date... I want from the opening of my system the textbox of all records are should be current system date.
View 14 Replies
View Related
May 1, 2013
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
View 10 Replies
View Related
Jul 31, 2014
I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.
We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.
The problem: For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.
I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.
View 3 Replies
View Related
Dec 4, 2014
How can I make certain my user enters records on a subform before attempting to save the main form? Right now they can completely ignore the subform before saving the record.The Main form has business address, etc. on it. the subform is bound to a join table that lists the multiple categories, subcategories and sector the business is listed in for a directory.
I already have my fields set to required at the table level in the join table, and have some existing VBA in both my subform (to update edited date) and my form (to validate empty records where a certain condition is met) but that's not the issue...
How do I focus the user to enter a record on the subform to the point where they are forced to enter something and complete the subform before the record is updated.
View 2 Replies
View Related
