Query Problem - Greater Value Required
Jan 11, 2008
Hi everyone.
What I currently have is 2 columns in one table 1 is required and 1 isnt.
However what i need to do is work out the sum value of the greater amount from each line.
IE
A B
1 2
1 0
2 1
which would give a total of 5 as its adding B1+A2+A3
I tried creating a 3rd columb and entered this IF Formula but apparently access doesnt support If formulas
=If([claims]![Mileage1 IR]>[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])
my plan was to get the "IF column to show me the higher amounts which could then be totalled up.
Any ideas where I may be going wrong?
Kev
View Replies
ADVERTISEMENT
Feb 16, 2007
Hello All
I'd appreciate some help with a (for me) pretty complicated query Im attempting. Im trying to achieve 1 answer if the source is less then 7, and another answer if it is greater.
I've been searching through archived posts and picking up tips to the extent that I think im in the right ball park, but Im making a mistake with invalid syntex.
in plain english Im trying to calculate
If:
date range from bookings1 is 7 or less
I want to multiply the result of the date range
By the daily hire rate (from a different table)
If:
date range from booking! is over 7
I want to multiply the result of the date range
by the daily hire rate
then multiply that answer by 0.8
(to achieve a 20% discount on the total)
Cost:IF([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]<7)*[Car]![Daily Hire Rate] IF ([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]>7)*[Car]![Daily Hire Rate]*0.8)
I've tried quite a few variations on that and it reads right to me, but im obviously missing some vital formatting.
I know that criteria I had for calculating the date range, and multiplying that by the daily hire rate works because I've tested that and saved that version
Any help on this would be greatly appreciated
Thanks
George
View 2 Replies
View Related
Feb 17, 2008
usingSELECT TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.City, TblCustInfo.State, TblCustInfo.Zip, TblCustInfo.SrvAmt, TblCustInfo.CID, TblCustInfo.BLCAT, Sum([TblCharges.Chargeamt]) AS SumOfCharges, Sum([TblPayments.Creditamt]) AS SumofCredits, ([SumofCharges]-nz([SumOfCredits])) AS RunBalance, TblMemo.InvmemoFROM ((TblCustInfo LEFT JOIN TblMemo ON TblCustInfo.State = TblMemo.State) LEFT JOIN TblCharges ON TblCustInfo.CID = TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CIDGROUP BY TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.City, TblCustInfo.State, TblCustInfo.Zip, TblCustInfo.SrvAmt, TblCustInfo.CID, TblCustInfo.BLCAT, TblMemo.Invmemo, TblCustInfo.CancelHAVING (((TblCustInfo.BLCAT)="m1") AND ((TblCustInfo.Cancel)="n"))ORDER BY TblCustInfo.Zip;is there a way to return only records resulting in greater than zero from the ([SumofCharges]-nz([SumOfCredits])) AS RunBalance part ???
View 14 Replies
View Related
Mar 13, 2007
In my forename field I have entries with just the initial. I would like my query to filter out anything less than 2 characters. Does anyone know the criteria format?
View 3 Replies
View Related
Sep 7, 2005
What I need to accomplish is an alert if a query finds any records. In other words, When the main page is loaded a query will run in the background. This query is looking for any records that need to amended within the next 30 days. What i would like to do is if the query finds any records that need amending an alert "form" or conditional formatting in a field with turn it red as an indicator that attention is needed.
My problem is how do i write a code to see if the record set from the query is greater than null???
any ideas would be greatly appreciated.
Ricky
View 2 Replies
View Related
Feb 10, 2014
I have a table.
BackupTbl
The table has 2 fields I want to compare.
Limit
Usage
I obviously want to know when they are over their limit. Tried using expressions but I either return all results or none.
I tried a bit of SQL
SELECT * From BackupTbl
WHERE Limit < Usage
This returns all fields.
View 6 Replies
View Related
Jun 29, 2005
Creating a query in Design View, how does one create a query in the "Condition" field that tells the Access to return the record set where the contents of one column is greater than the other?
Table Name=SURVEY_RAW_DATA
Column Name=TOTAL_EXP
Column Name=TOTAL_NX
There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers.
In SQL I would write it like this:
Select * FROM SURVEY_RAW_DATA
WHERE TOTAL_EXP > TOTAL NX;
Thanks in advance,
Ken
View 2 Replies
View Related
Mar 5, 2013
I have a list of companies that need to return paperwork, we are then scanning that paperwork and placing in a directory and listing that it was received in a table in Access. I want the button to view this scanned in item if the qry for that paper work has arrived and the count is >0.
I run a command on a single form that if a qry is >0 it shows the button and I added that to the GotFocus event in the form.
Me.CheckSuitability.Visible = (DCount("[ID]", "qry_Suitability_Papers_Needed") > 0)
But this is a Continuous Form and I would like the buttton to show next to the company if the count is >0.
View 1 Replies
View Related
Nov 24, 2013
same as the title goes, why i can't query those figures greater than 15% only? How am i suppose to query them right?.Here is my database you cant check query1 and query 2. I am using Ms Access 2010.
Link:
www dot dropbox dot com/s/n3iuev1c5e0dfby/StockMonitoring.accdb
xD: StockMonitoring.zip
View 5 Replies
View Related
Nov 3, 2014
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies
View Related
Sep 13, 2007
I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible?
Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.
View 2 Replies
View Related
Sep 12, 2005
Hi All,
I am relatively new to Access having created my first database a few months ago. I have hit a problem and I am not sure if I can solve this with queries or I need to move to Visual Basic.
I want to create a table containing the stock a product for each day for the next sixty days. I have the current stock in one table, a monthly forecast of sales in another and purchase orders in another.
I want to use the monthly forecast divided by how many days in that month and move forward day by day allowing for purchases coming in.
The tables (simplyfied) are :-
Stock Table.
eg.
Code Stock Qty Date
101 10 11/9/05
102 200 11/9/05
Forecast Table.
eg.
Code Month Fcast Qty
101 P09 30
102 P09 90
Purchase Order Table.
eg.
Code Due_Date PO Qty
101 13/9/05 100
The table that I would like to create would contain Code, Date and Stock.
eg.
Code Date Stock
101 12/09/05 9
101 13/09/05 108
101 14/09/05 107 ...........
102 12/9/05 197 ...........
Any ideas/direction would be welcome.
Carl
View 4 Replies
View Related
Dec 10, 2006
Hi,
I have my Query looking at multiple colums on a single table.
The table has approximately 2000 records.
I would like to create a Query that searches for criteria in one colum only. I have created a Query and providing i search for exactly what is written then it shows results, e.g. when i seach for Horse Furniture then i get many results but if i search for Horse i get no results?
I would like the search to be dynamic, so when the end user runs the query they are presented with a dialog box asking what they wish to search for (this i have no problems with) however i need to know how to get my query to be dynamic and provide results based upon single information.
is this possible and if so then how?
Thank you in advance for your help, i have been banging my head for a while on this one and am getting no where.
Bev:mad:
View 2 Replies
View Related
Mar 28, 2007
Hi All
Im after some help with a query im building.
I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.
Each site will take a quiz and the problem I am having is with the statistics side of things.
What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.
To summarise I need 10 percentage correct fields, broken down by site.
Please can anyone help?
Many Thanks
Dazstarr
View 5 Replies
View Related
May 17, 2007
Hi
I am struggling with something im sure is quite simple.
I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.
What I need to do is crosscheck the calls answered by employee in both tables. For example:
Table 1 has 3 records for John. (This means 3 calls answered).
Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).
What I need is a report with both these sets of data and a new field with shows the the difference between the two.
Can someone please help me.
Many Thanks
View 4 Replies
View Related
Jan 29, 2008
I have a table set up for training with the following fields. Date, Employee Name, Class Name.
I have entered all the current data. I want the database to tell me who doesnt have a class completed.
I also would like a code to put in my query that will show recurrent date, although there is different recurrent dates dependent on the class.
Any help would be appreciated!
Thanks in advance.
View 4 Replies
View Related
Apr 27, 2007
I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).
There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.
View 2 Replies
View Related
May 11, 2007
My database is built using Access 97.
I have the following table and 2 queries:
Table: “Materials Master Sheet”
field: “Material”
field: “Re-Order Level”
field: “Re-Order Quantity”
Query (crosstab): “Most Recent Count Numbers”
field: “Material Name” (joined to table’s “Material”)
field: “Count Quantity”
Query (crosstab): “Orders Pending Delivery”
field: “Material” (joined to table’s “Material”)
field: “Total Orders”
I am attempting to create a query called "Count vs ReOrder" with the following fields:
1. “Material Name” - comes from “Most Recent Count Numbers” query
2. “Order” - if the inventory count has reached the reorder level point, then 1, else 0; criteria: 1
expression: IIf([Most Recent Count Numbers]![Count Quantity]<=[Materials Master Sheet]![Re-Order Level],1,0)
3. "Suggested Order" - reorder quantity - count of any orders pending delivery; criteria: > 0
expression: [Materials Master Sheet]![Re-Order Quantity]-[Orders Pending Delivery]![Total Order]
This works great if all materials have orders pending delivery. However, if there are no orders pending (material not displayed in crosstab query), the material is not displayed (even though, in reality, the suggested order should be >0)
Currently does this:
material 1 - count = 1, re-order level = 2, re-order quantity = 8, orders pending = 4: shows material and suggests ordering 4 (correct)
material 2 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 4: material not shown (correct, as suggested order would be 0)
material 3 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 0: material not shown (incorrect! should be shows material and suggests ordering 4)
How can I go about addressing the missing “Orders Pending Delivery” numbers?
View 2 Replies
View Related
Nov 7, 2014
I've been trying unsuccessfully for weeks to try and query a usable data set that would later be used for calculated values in a report.
I'm building a Hospital Acquired Infections Database for my facility to record data and process reports automatically. Previously, I built the system in Excel using VBA and userforms, but the size of the file has become too cumbersome, and now takes too long to process my reports.
So here's the issue.
UNIT_DATA TABLE:
UNIT
INF_MONTH
INF_YEAR
PT_DAYS
CVC_DAYS
IUC_DAYS
VENT_DAYS
APV_DAYS
PT_LEVEL TABLE (There are many fields in this table for recording purposes but I will only list the ones I need for reporting purposes):
UNIT
INF_STE_MAJ
SPECIF_SITE
INF_MONTH
INF_YEAR
So the important numbers I can't seem to collect are the total number infections per month from the PT_LEVEL Table for only select SPECIF_SITE infections.
the Tables are joined Left to Right as Follows:
UNIT_DATA.UNIT - PT_LEVEL.UNIT
UNIT_DATA.INF_MONTH - PT_LEVEL.INF_MONTH
UNIT_DATA.INF_YEAR - PT_LEVEL.INF_YEAR
The UNIT_DATA Table is a monthly collection for the 5 metrics (PT_DAYS, CVC_DAYS, IUC_DAYS, VENT_DAYS, APV_DAYS) regardless of whether or not the number for each is zero. Every unit in the database will have a record for each month and year.
The PT_LEVEL Table will only have records if and when a patient develops an infection. This means there is no cumulative monthly data for the PT_LEVEL Table Data based on SPECIF_SITE infection type.
I need to first do a roll up count for every month and year in the database, for which I was initially trying to use the UNIT_DATA table for since it contains every month and year. The problem is when I try to query the SPECIF_SITE from PT_LEVEL, I can get the number of Infections for months where infections where present for each unit and null values, for each month, and each year in UNIT_DATA, but when I include a where condition to narrow the view to only selected SPECIF_SITE's it only shows data for that SPECIF_SITE for months where they occured.
I need a cummulative monthly aggregation of the totals for a selection of SPECIF_SITE infections, for every unit, for every month, and every year, since July 2010.
View 14 Replies
View Related
Dec 10, 2007
Hi
Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds.
1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of?
2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.
My knowledge of Access is Intermediate.
Kind regards
View 14 Replies
View Related
Jul 10, 2013
I am trying to create an update query. I am trying to update a field in a table with the current date as a request.
I have a table named tblTest and a field named Date2 that I am trying to update with the current date, the button that the VBA is applied to is in a form name frmTest. This is my code:
Private Sub Command39_Click()
Dim t1 As Date
t1 = Date
db.Execute("update tblTest set tblTest.Date2") = t1
End Sub
But when I press the button I get:
Run time error '424'
Object Required
It highlights the 4th of code....
View 8 Replies
View Related
Oct 23, 2007
Hi everyone!! I need some advice regarding the following query:
This is a big database with many tables and queries etc. but for simplicity lets say i have database with 2 tables (table1 and table2) both contain invoice numbers. table2 contains one record while table1 contains many. Ok simple so far but here comes the question, how can I write a query that selects all records from table1 that are greater than the value in table2, hope that makes sense? I've been trying to slog this out on my own but dont really know where start so any help will be appriciated
Thanks Steve.
View 3 Replies
View Related
Apr 6, 2008
Hey, cool forum. going to be really useful!
Have an assignment due in a few weeks and basically its a gym senerio.
Got a lot going so far, macros tables and forms. reports shouldnt be a problem.
Now i'm not sure this is actually expected but i'm sure i'll get high marks if i include it.
anyway:
there are 3 memberships, gold silver and bronze, gold can take 7 classes max and bronze 2 so how would that query work?
I can make it so no more than 7 member ID's are entered into a field but how will i make the queary recongnize the difference between gold and bronze?
would it be something like group by Membership and type criatera Gold and then in member ID have <=7?
if i did this how can i work that into a form?
anyway, know that might not make sense, or may make perfect sense but i just dont get it! either way thanx for reading, any suggestions would be much appriecated
View 4 Replies
View Related
May 26, 2006
I have an SQL statement that I finally have working after much help from this web site. But I want to enhance it to be able to search between two dates, I have on my search form txtStartDate and txtFinish Date. I want it to read > or = to txtStartdate or txt< or = to txtFinishDate. This is my code at present. Can anyone help please?
Private Sub cmdSearch_Click()
Dim strSearch As String
If IsNull(Me.txtExerciseName) Then 'this is to ensure user keys/select at least 1 criteria; add on rest of the controlnames as desired
MsgBox "Please enter at least one criteria"
Else
quot = """"
If Not IsNull(Me.txtExerciseName) Then
strSearch = strSearch & "SELECT tblTechnicalIncidentReport.Exercise_Name,BoxNo,Fau lt, Catagory,Time_Reported,Date,Brief_Description,TopT en,TFRNos FROM tblTechnicalIncidentReport " & _
"WHERE Exercise_Name LIKE " & quot & Me.txtExerciseName.Value & "*" & quot & _
"AND BoxNo LIKE " & quot & Me.txtBoxNo.Value & "*" & quot & _
"AND Fault LIKE " & quot & Me.TxtFault.Value & "*" & quot & _
"AND Catagory LIKE " & quot & Me.txtCatagory.Value & "*" & quot & _
"AND Time_Reported LIKE " & quot & Me.txtTime.Value & "*" & quot & ";"
End If
Me.Results.Form.RecordSource = strSearch ' Update the record source, this line is to be placed at the end of all your criteria's if end if codes
' Requery the subform
Me.Results.Requery
End If
End Sub
View 2 Replies
View Related
Aug 30, 2005
I managed to cludge around my earlier deciles query so now have the break levels though have run into a brick wall.
I have 2 tables (simplified below)
tbl1:
decilerank (basically a record number)
value
eg
1 150
2 160
3 170
tbl 2:
value
name
What I want to be able to do is be able to select all the names where the values lie between 150 and 160
algorithm is select tbl2 where tabl2.value > tbl1.decilerank[1].value and < tbl1.decilerank[2].value
and so on
Anyone able to help?
Edited to add:
Nothing links the tables.
Basically I am trying to use the data from one table as input into a query against the other.
eg select person where age is > 20 and < 30
where 20 and 30 are stored as seperate records in another table.
View 1 Replies
View Related
Jan 10, 2006
Hi,
I have a query setup, using a caculated field, to determine the greater of 2 dates, from 2 separate fields. Here is my calculated field:
LastContactDecide: IIf([Date1_Last_Contacted]>[Date2_Last_Contacted],[Date1_Last_Contacted],[Date2_Last_Contacted])
This works fine, except when there is a null value in either Date1_Last_Contacted or Date2_Last_Contacted. How do I get around this ?
Thanks !
View 1 Replies
View Related
