struggling 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, I have two tables, one being a subset of the other. What I want to do is write a query that lists data in the larger table that is not in the smaller one. In effect I want to subtract the data in the smaller table from the larger table and list the remainder.
I have tried something like:
Select a.oem from a,b where a.oem not in (select b.man from b);
This query does not complete and I get 100% CPU usage :( . The largest of the tables has roughly 4000 records in it. Any ideas? Am I doing something wrong?
Thanks
I have a field that will only get numeric values, numbers. And it will always have four characters or less. I do have times when I worry my data by over eighty's certain size or I average value, or both. Is this possible when using a text field.
View 14 Replies View RelatedI am using data from a sales and a finance system and essentially need to understand the difference between them and account for this "balancing" row.
My data is as follows
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, A, F123, 100, 1000, 10
January, A, F123, 800, 8000, 80
January, B, F123, 10, 100, 1
January, A, F124, 500, 10000, 1000
February, A, F123, 100, 1000, 10
February, A, F123, 800, 8000, 80
February, B, F123, 10, 100, 1
February, A, F124, 500, 10000, 1000
TBL_Finance
Month, Factory, Quantity, Sales, Margin
January, F123, 1000, 10000, 100
January, F124, 550, 10500, 1000
February, F123, 950, 9500, 95
February, F124, 600, 10000, 1000
I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9
January, C, F124, 50, 500, 0
February, C, F123, 40, 400, 4
February, C, F124, 100, 0, 0
what is the difference between creating a table using SQL and creating a table using MAOL (access object library)? I ran into these 2 methods when taking a tutorial on the internet? What are the advantages of each method?
View 1 Replies View RelatedI have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
View 2 Replies View RelatedHi 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 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 3 Replies View Relatedi 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.
What is the difference between "short text" and "long text"? Both seem to have 255 characters limit. I have Access 2013, and I found a solution, that you can use Memo instead of Short text. But the problem is, that there is no "memo" data type in Access 2013. So that won't work for me.
View 8 Replies View Related 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.
ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00
This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs
Like Below :
ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00
IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00
I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...
I am designing mileage database in access 2010.
table:
1- maintbl (date, truckNo fillinQty,rate,cost, odometer, mileage)
Query:
1- mainquery (based on maintbl)
Form:
1- mainfrm (Based on mainquery)
i want to calculate mileage :
example:new odometer reading - previous odometer reading / filled in qty
example: 2000 km - 1000 km / 200 liters = 5 liters /km
Objective: How query will automatically calculate the difference of new odometer reading and last odometer reading for a specific truck number and divide it with filled in qty of fuel?
Could someone clarify the difference between DAO and ADO?
We migrated to Office 2003 last year - some large organizations move at the speed of continental drift. The database is written in Access 2002 (finally have most of it up and running - with thanks), but I need to really understand the difference for the documentation - to see if there are any issues that might undermine the database during the upgrade.
Hey
How can I calculate the difference between two rows like:
value____diff
4________4
7________3
12_______5
/Thanks
I have a Transactoin Table that holds the following fields:
TransID (*Primary Key Numerical Value)
OrderID (Non Unique Number Value, One Order can have multiple Transactions attributed to it.
Status (Numerical Value ranging from 0 to 32, each is a different phase in the life of a transaction such as 0 means Transation Created, 1 through 31 are various actions with 32 beign the transaction closing)
Date (The date in which a particular phase of a particular transaction happened.)
Reason (The Description of a phase. This is the only text field whose contents are relative to the Status field in the same table.)
I need to find the amount of time passed from the first Transaction to the last transaction of each individual order, which will be displayed in a report. Unfortunately not every order goes through all 32 phases, in fact most dont. The individual orders have a random amount of phases that they go through but each have a starting one (which ranges from 0 onwards) and an ending one (once again it is not necessarily 32). The dates correspond to the numbers in an individual order which means that if an order has the following phases:
1
2
4
6
Then the dates of each phase will be one after the other like the Status Number as shown above. Anybody knows how to do this? I need to find how much time passed from the first transaction to the last for each individual order qand display it in a report.
This has probably been raised before but here goes anyway.
I have two fields in a table: time_in and time_out, both as date/time fields and formatted for short time with a mask in the form.
I need to calculate the time spent, i.e. time_in 10.00, time_out 12.15 therefore 2hours 15minutes.
any help much appreciated.
Jim
How 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
hey guys im not even sure if i have the heading right for this one.
I have a table example data attached,
what i would like to do is return all transfers from that table where all the item numbers in a batch dont equal each other i.e if you subtract them all in a batch it doesnt = 0
i have know idea how to do this and dont know if this is even the right way of doing what i need which is to show transfers where some1 has transferred a product to a different one which we do want to have happen we just need to know when it was done.
any suggestions?