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?
What the database currently has: A payment entry form consistenting of many fields. This form populates a payment entry table. Some of the fields within the payment entry form are linked to other tables and queries for data (such as a recipient list).
What the database now needs: A group payment option. Should ten people attend a dinner, the total cost needs to be divided among the attendees, and then the payment entry form table populated with ten different entries and the subdivided cost of the dinner per person.
My thoughts: The most ideal thing to do is to have a hidden recipients window show up when the user indicates that this is for a group event. The user could then add all the additional recipients (beyond the primary which is already collected on the form), and the total amount for the meal. The database would then generate an entry for each recipient listed, dividing the total cost among them, and then simply duplicating the rest of the information as is.
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2 4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
I'm fairly new to access and have been tasked with fixing a database that to my knowledge is built incorrectly but due to budget constraints and the time it would take to build a new one, we have to stick with this one. Here's my problem, in the database is a form that we use to create new entries in a table but when I try to create a new entry through the form, it creates multiple entries in the table with the rest of the data split between all the new table entries. Is this something that is caused by us moving over to the most current version of Access? Is there a way to fix this problem given the software we are using? At one point I had recreated this table because we had been having issues with other aspects of the database (security deposits were not being updated when entered through this form).
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!
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 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 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.
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.
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?
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 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.
im in the middle of developing a database to log DOWNTIME of various machines in the factory. Basically myself and others will use it to log breakdown details and the amount of time (in HH:MM) the machine was off.
I have a form which will list details of a certain machine in a list box which i can populate using a query.
The list box will display 3 columns: Date of Breakdown, Machine Name and Downtime
I then have a text box below this that i would like to display the TOTAL amount of downtime for that machine (The listbox may have different information in it depending on the query criteria, e.g. It might display a certain month for a certain machine).
THE ISSUE i am having is its proving to be difficult to get the correct sum of the accumulative time to display correctly in the text box.
Is there a way i can use a query to calculate the total time of a column and return a value which is formatted as time (like this hhhh:mm or dd:hh:mm) or preferably use some VBA code on the form itself to total up the DOWNTIME column of the list box and display it with the correct format in the text
I have gotten close, but as the total amount of time exceeded 24 hours, it went past 00:00. I'm assuming it treated it as a clock and went into the next day? This is NOT what i am after, i need the total amount of days, hours spent on the machine and not a time of the day.
Basically, I'm compiling a table of funding sources and whilst all the fields are relatively straight forward, how best to approach the matter of where the funding is available. For example, Funding A is available in America only, Funding B is available in America, Canada and Europe and Funding C is available Internationally etc (a huge number of possible options and infinite different combinations). How's best for me to lay this out because there will be hundreds of funding sources all with different availability criteria? If the field name is 'Countries funding is available', can a data type input be multiple entries?
Ultimately I'll want to be able to query show all funding sources available internationally or show those available to America and Canada only etc...and when I query on another field or show all, I'll want to see where each of the funding sources are available, listed out .
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....
In access 2007, I was wondering if it was possible to have multiple people enter information in a form at the same time and have it save? The database is split so that multiple people can be in the whole database at one time, but when two people enter information in the form at one time... only one of the two can save...
I'm quite new to creating a database. On my navigation form, I have a dropdown list/combo box that contains up to 16 possible selections. The user should be able to select more than one item, but can't, when using the navigation form. In the man form that this 'writes to' the selections can be made, with checkboxes in front of each item in the list; but not in the navigations sub-form. the Navigation sub-form only shows the list in a column. I don't see any properties that explicitly allow more than one suggestion or restrict to only one, so am I using the correct control?
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.
