I am making a query and I need to be able to determine if two number fields are with in 2.5% of each other. How would be the best way to go about doing this. I have two distances and I need to display them if there is a differenece of greater than 2.5% in them.
View RepliesHow can we calculate a difference of column
Example
when I have a table containing a single row i.e.
Numbers
66
45
23
52
523
563
452
133
Now I required a query that must show a difference like this
Numbers ------------------ Difference
66 ------------------------ 0
45 ------------------------ -21----------- (45-66)
23 ------------------------ -22 ------------(23-45)
52 ------------------------ +29 ------------ (52-23)
523 ------------------------ +471----------- (523-52)
563 ------------------------ +40 ------------ (563-523)
452 ------------------------ -111 ----------- (452-563)
133 ------------------------ -319 ------------(133-452)
So it is possible or not
Please tell me
Waiting for reply
Thanks
Ashish
Is there such a calculation in REPORT wherein I can have the Running Difference of several numbers?
Example is several Kilometer reading of a vehicle:
18716
18731
18742
difference of red color numbers: 15
difference of 2nd red color number and blue color numbers: 11
total: 26
I don't even know if my calculation is correct ...
I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.
These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.
Any VBA coding that will change both the "Format" and the "Decimal Place" Field Properties on the table I make (i.e. "DT"). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read "2"). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.
Is it possible to do this programmatically; I've search the internet high and low and could find anything remotely useful.
I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?
View 14 Replies View RelatedHow do I determine which forms, reports or other queries use a specific query in my entire database?
Thanks in advance.
I would like to know how to do the following:
Query 'X' is a sum query.
How do I determine which other queries use Query 'X'
I want to make some changes to Query 'X' but I want to be sure of which other queries will be affected.
Thanks
I have a form that shows employee license information. I also have a query & form based on that query to show licenses currently suspended. I would like to on my employees form to flash "license suspended" label if their name/record id is on the license suspension query. For all employees whose license isn't suspensed I would don't want anything to show. I can get the label to flash and to appear. I just need help evaluating the query and comparing it to the current record. Example I
if me.id = queryname.id then....execute label flashing, etc.
Can I write code to do this? Thank you
I have three tables for an event registration database based on Microsoft’s Event Registration template. The main tables are “Attendees”, “Registrations” and “Events”. “Registrations” is a swing table. Is there any way of creating a query to determine which attendees have not registered for a specific event? I have included a graphic of the table layout. I sure would appreciate some help on this. Thanks.
View 1 Replies View RelatedHi,
Im using square bracketed parameters in my query designs for user prompts. Now I want a similar feature to tell the query what field I want it to show. Is there a way of doing this please? - or does the fact I need to do imply a db design flaw?
I have a field on a report which I am struggling to produce the right query for.
I have a table - tblTillDept which consists of a 4 fields. The first is the PK, which is the number of a till department. The second - fourth fields are names of 3 different branches of the small garden centre I am writing the database for. The values in these fields are the names of the corresponding departments. Not every branch has exactly the same name for each field.
Prior to the report being is run, a small form, frmCentre pops up asking the user to select the Centre they are printing the labels for. They select the name of the branch from a combo box (cboCentre) bound to a field list of tblTillDept. When the report is formatted, the intention is that the value of this combo box selects the field from which the value for the department is taken. I can see that I need to set the control source of this property to some sort of query, but I can't quite work out how to do it.
I've uploaded the database, written in Access 2000, here (http://www.moppy.co.uk/plants.mdb) as it's too big to post as an attachment.
All of the types? Including Memo?
View 2 Replies View RelatedI have a query that runs just fine and takes about 1 minute to run on average. I have vba code that uses Dlookup to determine if the query returns any records. The problem is that each time it runs it has to crunch all the data, all I care about is if there is any result at all. Is there a way to get the query to stop after it finds one record to speed things up greatly?
View 2 Replies View RelatedI'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.
I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).
I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.
Things I have tried to no avail: Putting a Iif statement in the query criteria: gives an error that criteria is too complex Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run
I have a file that I want to import on a daily basis and append to an existing table in my database. The date changes each day. I want to create a query that checks to see if the date (of the first record) already exists before I import and append the new file. If it does, I want to show a message saying something to the effect of "This file has already been imported".
View 12 Replies View Relatedstruggling with this, hope someone can help.
I have a table of timesheets that contains records for staff but it only contains weekly record if they have actually created that weeks record.
I also have a table of weeks that contains all of the available weeks that time can be booked against.
How can I query the timesheet table against the weeks table and get a list of staff that have not created a record for that week ?
Hi all, bit stuck on a query I'm working on.
What I have is a table with three columns: Name, Start, End (the last two being a start time and end time column in 24 hour format). What I need to do is calculate the time interval in minutes between the start and ending times (this has been done already and quite easily). What I find myself stuck on is when the ending time exceeds 00:00 and the start time is before this. IE: 23:30 start, 00:30 end, which is 60 minutes (duh). I wind up getting a 23 hour time.
Is there a way that I can code up a query (has to be a query as the rest of the DB results rely on the correct minutes per person)? Appreciate any help in this
I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..basically the structure is
equipment number call number bas start date
12345678 112255 1/7/14
12345678 112256 3/7/14
What i'm after is the 4th column to work out the date diff... in this case 2 days the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..
I currently have a table with the following fields:
dtmDateintPressintCycleintProgramintLotysnAxialdtmFStartdtmFFinishdtmPStartdtmPFinishdtmDStartdtmDFinish
How might I go about querying the difference between dtmDFinish and dtmFStart for multiple records?
EX:
09/07/05-1-1-1-5681-NO-12:30-1:00-1:30-2:00-2:30-3:00
09/07/05-1-1-1-5681-NO-3:30-4:00-4:30-5:00-5:30-6:00
09/07/05-1-1-1-5681-NO-6:15-6:45-7:30-8:00-10:30-11:00
Would return two results: difference for 3:30-3:00 = 30 minutes and difference for 6:15-6:00 = 15 minutes
Thanks for your help!
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'm trying to make a query where it can calculate the difference between a table field value and a query field value.
Ex. Table INVOICE - fields
invoice no, invoice value,
Query TOTAL - fields
invoice no total invoice value
I need to get the difference between invoice value and total invoice value.
How can I put this into a query (invoice value - total invoice value)?
I am struggling with calculate difference between two query table values, I first created a make a table query(current meter reading) which contains one column called "meter read" , and I created a second make a table query(previous meter reading) which also contains "meter read" column, I linked those two make table queries to calculate the value difference between two date, how can I create a form to calculate diff between any two date?
View 3 Replies View RelatedI am VERY, and I mean VERY new to Access. I've been racking my brain all afternoon and googling like crazy. I just completed two levels of training on Access 2010 and have never worked with the program before. I already set up my tables and now I am on to querying. I have a table with several columns, two of which are "Start Time" and "End Time". I already created a query using the DateDiff function to calculate the time difference for each record. It output a new field with the time difference in hours. Now, I want to sum the totals of the time differences by month and I cannot for the life of me figure it out. My new query has Date (m/dd/yyy) and Hours.
View 14 Replies View RelatedDateDiff in a query
I am trying to calculate time difference between two time columns as follows in a query which has a few inner joins
Diff: DateDiff("h",[Table1.TimeStrat],[Table1.TimeStrat])
This gives problem, it is asking [Table1.TimeStrat] to be entered, where is the problem?
i want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
how to do it in access query.
I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:
tblRegion = RegionID
tblStatus = StatusDate
I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.