I want to add best 3 quiz marks from 5 quizzes appeared in a term. How can I build an expression for solving this problem in the expression builder.
It is possible in MSExcel writing this formula = Sum(Large(E3:E7,1),Large(E3:E7,2),Large(E3:E7,3))
Hi,
I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.
When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.
Hi, I am a newbie in MS Access. I am trying to write a simple database to record engineering drawings received. I would like to retain an entry into a field from a previous entry, but if it is slightly different allow the value to be overwritten.
For example:
If I receive a drawing from a particular person, (say) Osborne, then that is entered into the senders field. The next drawing I receive also from Osborne, wil already have Osborne in the entry field from the previous entry so I will not have to retype it. If a drawing is then received from someone else then Osborne is overtyped with the name of the new sender.
Anyone help a newbie with this please? Thanks
Hello,
I am running a database in which end users input values into a table via a data access page. The problem is that there are usually lots of records in this table which requires the same value for a particular field.
I was wondering if there is a way to implement something so that the user can input the value once and the value is then copied to all the records?
Many thanks in advance
Ok so here is the problem I need help with,
I have a prices table that stores all the different price changes for each part I have in my Parts table what I need to figure out how to do is to copy the "Last" price for that part from the price table into the Price field of the Parts table or have some way of updating it automatically either by macro or what not - example:
Price Table:
PriceID (PKey)
PartID (Connected to Part Number ID)
Price
Price Added( Date & Time when price was added so I can groupby "Last" or "Max" in the Price Query I have made)
------------------------
Parts Table:
PartID (Pkey)
Part Number
Price
-------------
Now how do I take the value my Price query gives me for "Last" or "Max" Price using the last date for that parts price and have it automatically get copied into the Parts Table price?
Thanx
I have a table similar to this
table
-----
ID
field1
field2
field3
'ID' is the primary key but I want MS-Access to have an eye on field1-3 to avoid entering the same information into field1-3.
How can I do this?
(Using field1-field3 as primary keys is not desirable)
Please help.
Thanks in advance.
xxyy
Hi there
This is probably quite easy but I cannot make it work so would appreciate some help. I have a table containing vehicle information. I have 2 fields, Current Mileage and Max Mileage. I want to do a comparison of the 2 fields and should the Current Mileage be greater than the Max Mileage, the query returns the Vehicle Registration. Test data has been entered in the table I am querying and I was expecting 1 value to be returned. However the query as I wrote returned either all the vehicle Registrations or none of them. Can someone help with this please.
Thanks in advance for the assistance
Craig:)
I'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.
Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.
I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.
Thanks.
Gayle Ann
I have a number of fields that hold values (scores - either 0,1 or 2) that need to be summed but in some cases the value is 99 (which is recognised by our stats program as a N/A type of anwer- This occurs when a question is optional and the user hasnt answered it. I did not want to value to be null (since its not like the user didnt answer it because they missed out on it by accident but rather they could not) and it couldnt be 0 either since 0 actually means something different in the context of the questionnaire.Lets say I have Q1 which has a value of 0, Q2 has a value of 1, Q3 has a value of 99 and Q4 has a value of 2I want to get the total of Q1+Q2+Q3+Q4 for a particular entry but if the value of any or all of the questions are 99 I want access to ignore it and just sum the rest of the values i.e. the sum should read 0+1+2+(99)=3 not 102I want to be able to include this sum function in the expression builder if possible since I am using it for a report. Can someone please help?
View 3 Replies View RelatedI am trying to generate a report, or even a word merge doc, that will print only the fields containing values above "0". I have to print a one page benefits report for each employee, yet not everyone has values in each available field. On each report, I only want to show the fields that actually pertain to the employee. Is this possible?
I have tried doing this as a Word merge, but the field names do not show up, only the values...I have been struggling with this for over a day...please help!
Hi there
I have an ASP form that saves numerical data to an access database via an insert statement in the asp page.
The main problem i have is that i have some code that adds all the stored values together and provides a total on the screen for the user to print off.
I would like to save this 'total' value to a field in the table but cannot seem to get this to work.
So i thought of running a query in access that would add the sum total of a number of fields and store it to the total field.
Is this possible and how would i go about it?
Cheers
I've put together a database for my company to track our archived files. When the files are archived, there are three important values pertaining to their location: a list number, a box number, and a barcode number.
Each box has a barcode on it, so what I'd like to do is make it so that when any record has both a list and box number entered (since it is dependent on the list number to know which, for example, "Box 1" I'm entering information for), it would automatically insert the appropriate barcode number in the field. It would obviously need a seed for each barcode number to be entered before it would know which one to use, but that could either be based on the first unique entry, or entered another way. If anyone can help me do this, I'd really appreciate it. Also, if my description was insufficient or confusing, just let me know and I'll try to clear it up. Thanks!
Example:
If I entered:
List # Box # Barcode #
77 4
It might not know what the barcode is the first time. But once I had entered
List # Box # Barcode #
77 4 366582
It would know what I was entering. Keeping in mind that I'm not showing the rest of the record, but just this end part, my next entry would look like:
List # Box # Barcode #
77 4 366582
77 4 366582
...the red representing that it would automatically fill that in, because it knows that if I'm talking about Box 4 in List 77, that must be the barcode.
I would like to make a report to show how many employees and which employees are attending to which colleges/universities.
In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.
There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?
For instance, this is my raw data:
Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC
I want my report to show:
Colleges -
University of HI
Bob
Sandy
Clare
John
Honolulu CC
Bob
Sandy
Kapiolani CC
Clare
Windard CC
John
I'm doing a medical db, where I'm trying to calculate ISS
Code:
en.wikipedia.org/wiki/Injury_Severity_Score
There are 6 values, and i have to sum the largest three (squares to be precise). How can I get three highest values from 6 fields in the same record?
Hi everybody. I got a access 2000 query that lists :
1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements this project)
7)Projectleader ( project leader name and number that is responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this project )
11)name (Employee name and initial and last that works for this project )
12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this
13)salary (amount of salary given to this employee) ===>i want cumulative for this
I want to create another query that lists :
A)cumulative value of hours worked on particular project task up that point.
b)cumulative value for wages given for that project task up that point.
http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample)
The above query ONLY lists hours worked and wages gives for particular project task only during
each week.But i want hours worked and wages give for particle project task up to that point in week. For
example a project task might have implemented last week but not this so i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular
project task.
Notes:
- There is a possibility that during a particular week no task been implement for particular project.
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report
http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view)
query that display hourly wages of certain project during each week
SELECT
querythisweek.weekno,
querythisweek.Year,
querythisweek.Project,
QweeklyReportHeader.Customer,
QweeklyReportHeader.Department,
QweeklyReportHeader.description,
QweeklyReportHeader.ProjectLeader,
querythisweek.Task,
dbo_Task.description,
querythisweek.Employee,
[lastname] & ' ' & [initials] & ' ' & [insertion] AS Name,
querythisweek.hours,
querythisweek.Salary
FROM
dbo_Task
INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;
code for querythis week( calcualte the salary and hours worked)
SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours
from dbo_Hourly_wages a
where dbo_Hours_worked.Employee = a.Employee
and dbo_Hours_worked.Project = a.Project
and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno)
from dbo_Hourly_wages b
where b.Year < dbo_Hours_worked.Year
or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS Salary
FROM dbo_Hours_worked;
Hi guys,
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip
billybobs 68135
dilberts 68137
billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Any help is greatly appreciated
Thanks
James
Hi,
i have created a query from just one table, which is basically a list of Financial transactions.
There is a field called supplier code, in this query.
Most of the transactions have a supplier code, but there are a few that don't.
i've got another table that has the supplier name against the supplier code.
i want to bring this table into the query, so that i can show the supplier names against those records in the query that have a supplier code.
when i add this table and link the 2 tables up and put the supplier name into the query. The query excludes the records with no supplier code.
This is presumably, because they contain null values.
I've been reading about the Nz function and i think this is what i need to use.
i've tried this, but it still only brings through the records with a supplier code and not all of them.
How i've done this is to go into the design mode of the query and in the column that would have null values i have click "build"
in here i have the following formula.
Nz([Supplier Name],"No")
Am i doing anything wrong?
Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!
View 12 Replies View RelatedHi,
I have a query which uses manual input on two fields. I would like to automate this query by pulling the input values from another table.
How do I read the input values from the other table instead of having the users type them in ?
Al
I have a subform with two combo boxes. These boxes select product catagory and products. The main form sets the supplier from which to select products.
If the user can not find the product they require they can double click thje product combo, which opens the product input form
I need the Products Form to be automatically populated by the following info:
Products catagory - from combo on sub form
Supplier name/id form parent form.
How do I make this work and/or pass these values as parameters to the products form.
I'm still floundering around with Access and I am stuggling!!
Thx.
Kev
I need to display a value from a table in a text field on a form based on another text field on the same form.
The table has records as follows:
Month/Year xx Month No xx Year No xx Month ID (xx shows break in columns)
Jan-06 xx 1 xx 2006 xx 24
Feb-06 xx 2 xx 2006 xx 25
Mar-06 xx 3 xx 2006 xx 26
Apr-06 xx 4 xx 2006 xx 27
May-06 xx 5 xx 2006 xx 28
Jun-06 xx 6 xx 2006 xx 29
Jul-06 xx 7 xx 2006 xx 30
In one text field I use the DateSerial function to calculate the value of the previous month (e.g.Jul-06). I need to return the MonthID value to the second text field based on the value stored against it is the table, so in case of Jul-06 it would show 30 in text field 2.
I have an Access 2000 project and am trying in a query view to join 3 values into one.
2 of the values come from tables, the third comes from another query view (all linked in the query design screen)
Value 1 is always populated, but for each record either Value 2 or Value 3 will always be empty.
All values are strings.
I have tried this formula in the query design grid:
dbo.Value1 + '/' + dbo.Table2.Value2 + dbo.vieTable3.Value3
..in an attempt to give the result Value1/Value2 or value1/Value3 according to the
particular record.
Unfortunately it doesn't work! ...just returns blank results when the query view is run.
Any suggestions would be greatly appreciated
thanks....nick
Hello Everyone,
I am in need of your vast array of knowledge and experience. I have been fighting with this Access report for a few days now, and I've finally decided to ask for your help.
I am running a report which provides a look at a set of 12 survey results. The 12 items are numerical in nature and are decimals with 2 decimal places. What I am trying to do is have Access automatically highlight the top 3 scores in one color, and the bottom 3 scores in another color.
So what I need is an expression that allows Access to discern the top 3 and bottom 3 from the series of 12 scores. in Excel you can identify the top score with this with the formula "Max(A1:A13)", but seeing as in Access we don't have the cell identifiers, this approach will not work.
I know that I will need to use conditional formatting in order to get the cells to highlight. I also see that there is "Max" and "Min" functions in Access as well, and I attempted to create and expression like this "=Max([field 1] AND [field 2] AND [field 3] AND, etc...)" with no luck. I also tried changing the "AND" to "OR", again without luck.
One thing that I should mention is that the report does contain a lot of other data, so I need to be able to apply whatever the best fix is, to only those 12 fields.
Unfortunately I am not accustomed to using code, and as a result I am unsure of how to input it properly. I am certainly open to code solutions, but I would also need a explanation of how to put it in properly.
The easiest solution for me, if possible, would be an expression that I can enter into the conditional formatting wizard to tell it to highlight the values.
If anyone has any ideas, I would appreciate it. I'll buy you a virtual beer :-)
I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.
I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.
Code:
IIf(IsNull([zam_adm]),"","ADM") & [zam_adm] & " " &
IIf(IsNull([zam_cts]),"","CTS") & [zam_cts] & " " &
IIf(IsNull([zam_crs]),"","CRS") & [zam_crs]& " " &
IIf(IsNull([zam_IPB]),"","IPB") & [zam_IPB]& " " &
IIf(IsNull([zam_opr]),"","OPR") & [zam_opr]& " " &
IIf(IsNull([zam_owl]),"","OWL") & [zam_owl]& " " &
IIf(IsNull([zam_pmi]),"","PMI") & [zam_pmi]& " " &
IIf(IsNull([zam_wl]),"","WL") & [zam_wl]& " " &
IIf(IsNull([zam_sgt]),"","SGT") & [zam_sgt]
The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?
I am trying to modify an inventory management database. I want to prevent duplicate entries to specific fields in centralized table. The table is called Work Stations. Its function will be to track various computer equipment by a specific work station name. Each of the items will have a unique asset tag.
The primary Key for Work Stations is WS-ID. The fields I am trying to prevent duplicate entries in are WS-Computer, WS-Docking Station, WS-Monitor_1, WS-Monitor_2, and WS-Switch. Each of those fields are primary keys in 4 different tables that conation more detailed information about item. The exception is WS-monitor_1 and WS-Monitor_2 have a one-to-many relationship with Mon-Asset Tag in the monitors table.
The goal I am trying to accomplish is when a work station ID is created or modified duplicate entries are prevented to those fields listed above. The computer, monitors, docking station, and switch fields in the work station table may contain data or may be null.
I have tried to set the Indexed Option to Indexed (No duplicates) for each of the fields and I have also tried setting Yes to Ignore Nulls in the index option on the table design tab for the individual fields. Both options have returned the same error stating the changes would create duplicate values, in the index, primary key, or relationship.I have not created form for this table yet as I was trying to get no duplicates option to work first. I have verified the data and the only duplicating fields are the Null fields
I got one months table containing a reporting_month ,timeperiod and an Index column ID with data type Autonumber.Basically I want to search through the table whenever the User types in a new reporting month or timeperiod over the dialogue.Now I want to realize the following options:
1. The user types in a new reporting month, when a record in the months table exists with a timeperiod and a blank reporting month field, it should be assigned there. For example the User types in reporting month = 032014 it should be assigned to the Time_Period Value = 042014-032015
2. Vice versa, the user types in a time period. This value shuold be assigned to the blank field beneath the existing reporting month.