Find The First Occurrence Of Multiple Record Entries
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.
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.
We currently have an Access database that is designed to keep track of a number of calling cards that are issued to our clients. The serial numbers given to us are sequential when issued in batches of 50 or more but, when another batch is ordered the serial numbers can be out of sequence by anywhere from 100 to 10,000 numbers. At present I am the only one who is entering the serial numbers into the system, this is done by importing from an Excel spreadsheet or from a text file. What I want to do is, add another form which will allow the other operators to add the data themselves by entering the start and the end of the batch seqence rather than entering them in individually or having to teach them how to import data without overwriting the current information. Can anyone help me?????
I have created a DB to hold training records for a company and have been using it for some years. When I record training I have to enter the data for one person at a time which is fine when you only need to do 10 or 12 but recently I had twenty people complete the same training and I don't really want to enter them one at a time.
What I would liket to be able to do is to bring up the Training Form, select the one Training Description (TrainingDescriptionQuery) and then select multiple Employees from the EmployeeDetailsQuery.
I am trying to create a database for a company I work with which creates a food product which needs to be traceable from creation to finish. I have created a table with all of the necessary information for the creation of a batch.
1. During this batch creation, we add water multiple times which we record each time, i.e., 2cups, 2 cups, 2cups, etc. I have a field for "water", and wish to record each instance and amount of water added, but only want one record for the complete batch. Also I only need the total water amount in the batch record, but want to be able to expand to see the individual amounts as needed.
2. Also, we need to record various readings (temp, etc.) throughout batch creation. Again, same problem as before.
What is the best way to do this? I want to be able to reference this info anytime I want to check creation specs for each individual batch.
I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.
I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.
I have no idea how to accomplish this...can anyone help?
I Have a forms which brings up a record in textboxes when you hit a search Button using a value that you type into a Textbox called BarTxt. My problem is that this value may not be unique. Therefore I'm trying to find and update the table using 2 values from the table (Barcode and PurchaseOrder).I have entered the code
Code:
Private Sub SearchBtn_Click() PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'" And "PurchaseOrder ='" & [POTxt] & "'") End Sub
This however is just bringing up a Type mismatch error. Even though both are text fields.Also even if I just use Barcode to search which works. My update Code
Code:
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & [BarTxt] & "'" AND PurchaseOrder ='" & [POTxt] &
I get an Compile error saying "expected expression".
Essentially I want to find out which of the entries in my master table have matching entries in my other tables, and list the ones that do.
So if I have an entry in the master table for “productA”, and there are also matching entries for “productA” in tables “SupplierC” and “SupplierD”. I want to perform a query that will output a list showing “SupplierC” and “SupplierD” (I have A & B tables but if there isn’t an entry in them for “ProductA” I don’t want them on the list.)
I intend to use the results to populate the values of a combo box in future so I require the list to be in a single column, rather than across many columns. Does anyone know if this is possible?
My master table is called “OurProductsTable” and the four supplier tables are called “SupplierA”, “SupplierB”, “SupplierC”, and “SupplierD”.
Each Table has a primary key called “ProductID” and I have linked them together on the relationships screen.
I’m not sure if this is the proper method but I also made another field in each of the supplier tables called “CompanyName” and set the default value as the name of each supplier, so if the entry for supplierC matches the master table entry I can return the “CompanyName” value of “SupplierC”.
Here’s what I thought the code should kind of look like but I don’t know how to apply it properly in a query:
I have a database where several entries may have the same address, but someone has been inputting some of the zip codes incorrectly. I want to come up with a way to get a query or the like to spit out a report with just the entries that have identical addresses and conflicting zip codes, and have hit a brick wall.
There is an issue that I cannot resolve in MS Access:
I have a large files with multiple columns that has 2 columns that randomly contain duplicated data.
I would like to filter out the duplicated entries in these 2 columns without spoiling the data integrity - and so the duplicates appear on the same row.
I think what I want it pretty much like conditional formatting in excel, but unfortunately the file size is really large to be filtered in excel.
I'm new to Access and databases so my question might seem dumb, but I need some help figuring something out. Well, I have this database where I'm storing information from different charities. So I created a table with their name, address, phone, contact person, and many othe things. Now I need to create a field with the services that they offer. Each charity could be able to offer more than one type of service. So for the services field, I cannot create a drop down list because that only allows me to choose one service. So what would be the best way to allow the user select more than one kind of service offered from a list and then be able to search that with a query? I have a list with over 50 different possible offered services and each charity usually offers 2 or 3 different services.Thanks
This might be confusing so I will try to give as much detail as possible.
I have a table that has part numbers and the number of that part used that is based on a construction group lets call it.
I have another table that has all the different products that we sell, it also contains the "constuction groups" that are needed.
My append query looks to see which product is being ordered, gets the "construction groups" then looks at the other table and gets all the part numbers and quantity used in that group. It takes the part number,quantity used and appends them with an order number into another table. This works fine, however because some of the "construction groups" use the same part number I have multiple entries for some parts.
I want to be able to make it so that when there are multiple entries where the order number and part number are the same, it adds the 2 quantities and leaves only one record.
If someone out there even understands my problem could you help me. I have been trying to get this part of my db workign for a few weeks now.
I was just wondering if its possible, i'm sure it is....... I've got a query to pull out invoices for a certain Project it brings up all project invoices and puts it into a report which works fine...... what i want to do tho is perform the same query but after i type in project code in criteria [] i then want to type peoples name or ID's that i want to print as some will be sent at different time intervals and i don't want to have to sort through invoice's to then send? is it possible? any one got any examples?
I would like to know if there's any way of fitting multiple entires of data into one field in a table. For example, a field for addresses that might be able to hold two or more different addresses. Must I create another column/field to hold how many different addresses that that person might have? Thanks!
i have a report that displays 12 fields of date sorted by a case number. I have a problem when there are two or more entries in the db with the same case number, but different data in the other 11 fields. My report displays each of entries in the db for the case number, but carrys the the data from the first of those entries through out all the entries. I'm not looking for an exact solution here, just for someone to get me pointed in the right direction. Is it a sorting and grouping error or something of like?
I have a tblhealthrecord that has many multiple entries for the same animalID. Would like to pull up all records from this tbl for one animal. I can sort them later but just need to know what to ask for so I can read upon them. I at first thought this could be based on date but not really since you could have multiple entries in one day.
I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.
I have a query that feeds a report. This report shows a "Set" and the "Charge Numbers" associated with it. Each set could have a possibility of more than one set of IT numbers. This means there could be say 2 entries for set "D7" and IT numbers for both of them.
As a result when I run the query, and say the set has 2 Charge numbers, it will bring back 2 results, with 2 charge numbers on each of them, which is correct. So if a set had 4 charge numbers assigned to it, 4 results would show with 4 charge numbers for each result.
Is there any way I can make it only show one of those results as they are identical?
Hello, I have searched the forums and found plenty of information about eliminating duplicate values. What I would like to be able to do is run a query and have it show me only the records that have the field "serial number" that contain the same number. That is, I want to be able to query and see which serial numbers have been entered more than one time. The serial number is not the primary key, this database keeps track of product repairs and is used to log them in by the primary key serial number/date. Thanks in advance for any help. Tim
Hi all Can someone help me with an Append query problem I am having. The end result I need is a Work sheet that has a Customers details, Plant details, Work required and a Subcontractor name. I have a search function that I use to select either a Customer name or Plant ID. Once I have selected the Customer name/Plant ID, I use a button to append the Customer Name and the Plant Id to a Plant_History table. This table uses an Autonumber field to create a unique identifier for each entry which I am using as the Work Sheet number. I have then added coding to open a form which accesses the Plant_History table directly so I can enter the work required and the contractor to carry out the work. My problem is that the Customer and/or Plant will have multiple entries in the Plant_History table. Currently, when I select the button to append the details to the Plant_History table, if the Plant ID is already in the table, the number of records is doubled in the Plant_History table (eg if the record has previously been entered 4 times, 8 records will be appended to the table). I am not sure why this happens, if someone could point out the fault in my work, it would be much appreciated. Regards Craig
I have a database with information on carriers, lanes, and prices. I need to choose the cheapest 3 carriers for each lane. The lanes are either 3-digit zip code to 3-digit zip code or state to state (410-300 or CA-GA, etc.) So for each of these lanes, I need to find the 3 cheapest carriers all in one query. So the resulting table should look like:
I need to create a form that will allow the user to enter details about say a water meter - make, model, size for example.
These will be from combo boxes.
Each time the form is opened though, there could be a different number of meters to enter, from 1 to xxxxx
I will need to use this method for entries on other things as well, such as users and a couple of other things. Same basis just different information.
What I want to do is have a form where they enter the number of each thing that is going to be entered, so 3 water meters, 2 users. Then hit "next".
That will then open a form with the right number of spaces to enter the details for 3 water meters, or 2 or 1 or 999 depending on how many have been entered.
As apart from putting a look in and closing/reopening a single entry form each time.
Let's say if I am building a database for a library and in a table the book titles are the primary key and there is a field "authors" to record the authors' names.
But sometimes a book may have several writers and if I type all of them into one field, Access only recognise as one person. So how can I format/set up so that when I use query or filter, each one of names can be identified?
If multiple entries are not feasible, how else should I design my tables?
SHORT_DESCRIPTION unable to view unable to send out some stuff what is going on
Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:
Unable 2 to 2 view 1 send 1 out 1 some 1 stuff 1 what 1 is 1 going 1 on 1
I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.
e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.
Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?
Below is the my VBA code so far, which produces the above result.
SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date])) ORDER BY tblEmpSalaryHistory.next_review_date;
My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.
