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.
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
I have a real estate database.
I have a table of;
Purchaser's Requirements tblApplicantRequirements
Property Price tblPropertyPrice
Property Bedrooms tblPropertySummary
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).
Any help to fix this much appreciated.
Thanks, J.
Basically in the query I need a Collumn to only output the greatest value that is given within the sub query. If that makes sense?
Eg. Tenancy start date must be the highest date against the date the property was added back to the system - for post development properties.
At present the it's returning previous tenancies from pre development.
I have 3 fields with numeric values in them. I just want to know which one is greater (dmax or something) and lowest (Dmin or something)
Example
Field1
6
Field2
5
Field3
7
How do I dmax 3 fields? this would return 7
Dmin would return 5
I have a field in an MS Acess query that has records like - 1,000; 534; 769.13; 0; 654; 0, etc. It can also have few null records.I want to calculate the average of all records which are greater than zero. Ignore zero values or null values.
View 4 Replies View RelatedI have 2 tables linked with a 1 to many relationship by NAME. Tables look like this....
TBL-STORE
NAME ADDRESS
Walmart 12 Nowhere
Target 14 Somewhere
Lowes 10 Anywhere
Sears 16 Nowhere
TBL-PURCHASES
NAME [NUMBER OF ITEMS] [PURCHASE DATE]
Walmart 4 1/4/14
Walmart 2 1/5/14
Target 0 1/5/14
Lowes 3 1/5/14
Sears 1 1/5/14
Sears -1 (returned to store) 1/6/14
I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.
This is one way I've tried to write the SQL, but I keep getting an error...
SELECT Count([TBL-STORE].[NAME])
FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME]
WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1)
HAVING ([TBL-STORE].[NAME])="DISTINCT");
I also tried an Count(IiF( and can't get that to work either..
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
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 RelatedIn 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 RelatedWhat 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
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
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
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.
I tried doing a search but couldnt find the i needed.
Category_name Sku
cars 1
Trucks 1
Bikes 1
Phones 2
Tablets 2
Hats 3
and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku.
so it looks like this
Joined_categories sku
Cars|Trucks|Bikes 1
Phones|Tablets 2
Hats 3
And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together.
I have two tables:
Budget
Actuals
I have these fields in both tables:
MONTH, Organization Code, Account, SubAccount, AMOUNT
I want to Compare the results in a single query. However the amounts aren't correct and after a full day I cannot find the answer.
Code:
SELECT [Budget].Month AS [MONTH], [Budget].[Organization Code], [Budget].Account, Sum([Budget].AMOUNT) AS [Budget Amount], Sum([Actuals].AMOUNT) AS [Actuals Amount]
FROM [Budget], [Actuals]
GROUP BY [Budget].Month, [Budget].[Organization Code], [Budget].Account, [Budget].SubAccount
HAVING ((([Budget].SubAccount)="K08"));
I have a table containing the area, location and name of someone there is also a field contains a number from 0-10. I need to run a query that will count the number of times a number appears in this field and put this total in a field on its own.
this my table fields are as follows...
Area Location Name Number
And I would like the query to show....
Area Location Name 10's 9's 8's ect...
Is there a way to have a query return only records where the text in two fields doesn't match?
View 1 Replies View RelatedI have a query that is used to capture all the fields that can be possibly used.When run & exported to excel the user then simply deletes the columns with the fields that they don't require.We do this so we don't have to keep creating queries over and over again when you just use one that does all but then delete what you don't need afterwards.
Any way of using a form with tick boxes for each of these fields so when they user ticks the fields they require reporting on then only those fields will be returned in the query.
I have a query that selects different values from different tables.The values of these fields change all the time and I would like the result of my query to exclude - hide the fields that are zero. However, I only want to hide the specific zero fields not the hole record.Non zero fields should still be visible.
View 10 Replies View RelatedTrying to Compare 2 fields Status with answers Yes or No, from 2 Tables PipeLine and Pipe, and get the fields that don't match, what am i doing wrong?
SELECT PipeLine.[Project Name], PipeLine.[Capacity DC], PipeLine.[Project Num], PipeLine.[Status]
FROM PipeLine LEFT JOIN Pipe ON PipeLine.[Project Name] = Pipe.[Project Name]
WHERE (((Pipe.[Status]) ="Yes");
I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?
View 4 Replies View RelatedI have a query that is based on three queries. 2 of those queries have the same fields but return data for different dates. The 3rd query returns the dates needed. In other words, I am trying to return all results from both queries. these are the results I want:
Report_ID Report_Date Contents Approvers
Standard ops 5/1/13 daily operations rpt tom tomlin
royer manuf 7/1/13 syndication rpt rob davis
etc.
These are for year-t0-date. there is a YTD query that returns all dates needed. The other two queries return records on different dates, on is for dates <5/5/13 and the other is for dates >5/4/13. I have listed the SQL below. It returns the correct records, but for the Record_ID it returns -1 in each field instead of the report_ID text. So, I need to get the other fields to show up correctly.
Code:
SELECT [Daily_rpts_YTD_due_before_5_5_2013]![Report_ID] Or [Daily_rpts_YTD_due_after_5_4_2013]![Report_ID] AS [Report ID], due_dates_Daily_YTD.Due_dates
FROM (due_dates_Daily_YTD LEFT JOIN Daily_rpts_YTD_due_before_5_5_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_before_5_5_2013.Due_dates) LEFT JOIN Daily_rpts_YTD_due_after_5_4_2013 ON due_dates_Daily_YTD.Due_dates = Daily_rpts_YTD_due_after_5_4_2013.Due_dates
WHERE (((due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_after_5_4_2013]![Due_dates] Or (due_dates_Daily_YTD.Due_dates)=[Daily_rpts_YTD_due_before_5_5_2013]![Due_dates]));
i have a access table (AUTH) with following field
Company Auth Held
Tata 12
Dell 11
HP 21
Opera 11
Bangour 10
i used the following query to calculate the Held from a table named pers.
SELECT AUTH.company, AUTH.Auth, Count(AUTH.company) AS Held
FROM pers INNER JOIN AUTH ON pers.company = AUTH.company
GROUP BY AUTH.company,AUTH.Auth
then i got the result as under
Company Auth Held
Tata 12 02
Dell 11 08
HP 21 20
Opera 11 12
Bangour 10 12
now i want the difference between Auth and Held as Sur/Defi
how can be it done.
I am making a parameter query that looks up quality data by lot number. For some of the lot numbers certain fields of data may be null. How can I omit these fields in the query if they are null?
OR automatically omit them when exporting them data to excel?
I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007. The file reports monthly data and adds the new month to the right of the historic in a layout like this:
Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.
I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).
I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.