SELECT 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.Invmemo
FROM ((TblCustInfo LEFT JOIN TblMemo ON TblCustInfo.State = TblMemo.State) LEFT JOIN TblCharges ON TblCustInfo.CID = TblCharges.CID) LEFT JOIN TblPayments ON TblCustInfo.CID = TblPayments.CID
GROUP BY TblCustInfo.Name, TblCustInfo.Addr, TblCustInfo.City, TblCustInfo.State, TblCustInfo.Zip, TblCustInfo.SrvAmt, TblCustInfo.CID, TblCustInfo.BLCAT, TblMemo.Invmemo, TblCustInfo.Cancel
HAVING (((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 ???
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
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?
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???
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?
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;
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.
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.
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.
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
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
im having trouble setting up a query in a sports day database i have to complete for school. in a form i have a combo box which allows the user to select an age group. a query is then performed using the selected age group and a report generated. the agre groups are under 13s, under 14s, under 15s, under 16s and open (open is anyone over 15 years old). the query for under 13 to under 15s works fine, however im not sure how to get the 'open' query to work. in the combo box i have the value for 'open' set as '>15', but access doesnt seem to like this and no records are selected from my tables, even though i have entries who have an age greater than 15.
does anyone have an idea how i can solve this? i would prefer to use just one query which gets an age group from a form rather than have an individual query for each age group.
I am trying to match the bedroom and price requirements within the Purchaser's Requirements table to their respective fields within the Property Price and Property Bedrooms.
My query is thus;
SELECT tblApplicantRequirements.ClientID, tblPropertyPrice.PropertyID, tblPropertySummary.Address2 FROM tblApplicantRequirements, tblPropertySummary AS tblPropertySummary_1, tblPropertySummary INNER JOIN tblPropertyPrice ON tblPropertySummary.PropertyID = tblPropertyPrice.PropertyID WHERE ((([tblApplicantRequirements]![PurchasePrice])>=[tblPropertyPrice]![PropertyID]) AND (([tblApplicantRequirements]![Bedrooms#])<=[tblPropertySummary]![Bedrooms#])) GROUP BY tblApplicantRequirements.ClientID, tblPropertyPrice.PropertyID, tblPropertySummary.Address2;
The problem is that the query is returning houses that fit one requirement (ie. they might be lower in price than the applicant is willing to spend) but not the other (ie. they may have 3 bedrooms when the applicant requires 4).
hey guys i have the following query but the greater than part isn't working it is still showing all records
SELECT AREATBL.AREA_DESC AS AREA, IDEPTBL.IDEP_DESC AS DEPARTMENT, ITEMTBL.ITEM_DESC AS ITEM, ITMATBL.ITMA_PRICE_1 AS RRP, IIf([ITMATBL]![ITMA_PRICE_1]<>0,(([ITMATBL]![ITMA_PRICE_1]-([SCSTTBL]![SCST_LAST_INV_FINAL_COST]/[ITEMTBL]![ITEM_CTN_QTY]))/[ITMATBL]![ITMA_PRICE_1])*100) AS GP,ITEMTBL.ITEM_NUMBER FROM ((ITMATBL INNER JOIN (ITEMTBL INNER JOIN IDEPTBL ON ITEMTBL.ITEM_IDEP = IDEPTBL.IDEP_NUMBER) ON ITMATBL.ITMA_ITEM_NUMBER = ITEMTBL.ITEM_NUMBER) INNER JOIN AREATBL ON ITMATBL.ITMA_AREA_NUMBER = AREATBL.AREA_NUMBER) INNER JOIN SCSTTBL ON (ITEMTBL.ITEM_NUMBER = SCSTTBL.SCST_ITEM_NUMBER) AND (AREATBL.AREA_SYSC_NUMBER = SCSTTBL.SCST_SYSC_NUMBER) WHERE (((AREATBL.AREA_NUMBER)=[Forms]![FRMGP]![Combo16]) AND ((ITMATBL.ITMA_PRICE_1) Is Not Null And (ITMATBL.ITMA_PRICE_1)>0) AND ((ITMATBL.ITMA_ACTIVE_YN)="Yes") AND ((ITMATBL.ITMA_SEND_TO_TILL_YN)="Yes") AND ((IIf([ITMATBL]![ITMA_PRICE_1]<>0, (([ITMATBL]![ITMA_PRICE_1]-([SCSTTBL]![SCST_LAST_INV_FINAL_COST]/ [ITEMTBL]![ITEM_CTN_QTY]))/[ITMATBL]![ITMA_PRICE_1])*100)) >[Forms]![FRMGP]![txtTARGETGP])) GROUP BY AREATBL.AREA_DESC, IDEPTBL.IDEP_DESC, ITEMTBL.ITEM_DESC, ITMATBL.ITMA_PRICE_1, IIf([ITMATBL]![ITMA_PRICE_1]<>0, (([ITMATBL]![ITMA_PRICE_1]-([SCSTTBL]![SCST_LAST_INV_FINAL_COST]/ [ITEMTBL]![ITEM_CTN_QTY]))/[ITMATBL]![ITMA_PRICE_1])*100), ITEMTBL.ITEM_NUMBER;
I have a function that I would like to max out at .2 and I don't know how to go about doing that. Currently it is Equip Inf: (([Equipment]*(0.03*(2006-[Letting Date]))+[Equipment])/([Equipment]))-1 and then I multiply (1+[Equip Inf])*[Equipment] to get my inflation. What I want is for Equip Inf to never be greater than .2 or 20% What is the best way to do this?
I tried the Crystal Reports forum, but no responses. Can anyone see what I'm doing wrong with the below statement in Crystal Reports 8.5. I'm trying to select reecords that exceed 7 days based on a Crystal Reports parameter. The parameter has the format of 2/27/2006 (Discrete value). The 'LastRouteDate' has a format of 'datetime'. Thanks ! ! !
Hi, The query statement below is meant to extract the number of References with a type code of 'M' on the table tblHDRef, then present it under a column named 'Modified':
Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?
Code: Private Sub txtQty_AfterUpdate() Dim rs As DAO.Recordset DoCmd.OpenQuery "qryQuantitySoFar" Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
I would like to know how to make a checkbox indicade when there is something typed in a column.
Example:I have a table containing books with columns such as title, autor, etc. And I also have a column "notes" where I can make comments about the book.
But when I'm printing out the table in a form or report, I would just lite to indicate that there is something written about the book, and not write out the whole comment.
So how do I tell my checkbox (or other indicator sugestions) to indicate when lengt of column > 0?
