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
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 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?
I have an existing table with a field labelled Job Number (17 Job Numbers). Each month I import a new table and match each record via another field (serial number). For each new record, one or many, I want to attach a sequential Job Number. In this example the new job numbers need to start at 18 then 19 etc. How is this possible? I am fairly new to Access so please be gentle!. Cheers
View 2 Replies View RelatedHello:
I have a report with three text boxes:
Box 1 contains an employee's total weekly hours in hours and minutes: 40:00
Box 2 contains the actual total hours worked for the week: 35:30
Box 3 is the difference between Box 1 minus Box 2
I tried all the date/time functions to calculate the difference between Box 1 and Box 2 and none of them worked. I keep on getting an error message. Does anybody know how to calculate the difference between two sets of time (in hours and minutes)?
Thank you in advance.
Folks,
Newbie to this forum so please be gentle. I am a relative newbie to acces but i seem to be picking it up quite quickly.
My problem is this:
I have an enquiry form made up and a button on the form that will generate a number by using the following code:
Private Sub EnqGen_Click()
On Error GoTo Err_EnqGen_Click
Me![Enquiry Number] = NewEnqNum()
Me![Customer Name].SetFocus
Me![Enquiry Number].Enabled = False
Exit_EnqGen_Click:
Exit Sub
Err_EnqGen_Click:
MsgBox "Error " & Err & ": " & Error$
Resume Exit_EnqGen_Click
Exit Sub
End Sub
Public Function NewEnqNum() As Long
On Error GoTo NextEnq_Err
Dim lngNextEnq As Long
lngNextEnq = DMax("[Enquiry Number]", "Enquiry") + 1
NewEnqNum = lngNextEnq
Exit_NewEnqNum:
Exit Function
NextEnq_Err:
MsgBox "Error " & Err & ": " & Error$
Resume Exit_NewEnqNum
Exit Function
End Function
This code works perfectly and generates a new enquiry number for me.
The end user now wants to change the format of the number to read like this 06-0001, whereas before i was using 60001.
What i would like to know is there anyway i can increment the last 4 digits only? i.e remove the first 3 digits, 06-, increment the 0001 by 1 then reassemble them back together.
Thanks for your help.
How do I calculate the values between two columns and populate a third column with those new values? I have an ''Actual Cost'' column and ''Budget Cost'' column and I would like to create a third column ''Margin.''
Is this something that needs to be done with a query or can it be handeled in the design view of a table? I'm starting to learn that Access works nothing like Excel.
Thank you in advance for any help. I scanned some of the threads in this forum, but many were beyond my comprehension. Any walk-through of this procedure would be much appreciated.
I have four fields
Date Sent (date) Time Sent (Time) Date Recvd (date) Time Rcvd (time)
I want to know how many hours have elapsed between date and time sent and date and time rcvd
Any Ideas please as I am having a blonde moment
Hi guys am not sure if i got dis in d correct forum (forgive me if not)
Just wanted 2 know what's d diff btwn a thread & a post (if d's any)
Noks
Hi there
I am trying to get a very simple thing done in Access and I must have spent almost over 2 days of Internet time to do so. My problem is that I need to get the difference in 2 records to show in a query.
e.g. MyTable has 2 fields: 1. MyDate 2. MtrReading
Everyday I enter date in MyDate and a number in MtrReading. And I need to find out the difference between today's MtrReading and Yesterday's Mtr Reading.
Any help is greatly appreciated!
Thanks
Can someone tell me the difference between 'Call' and 'DoCmd' and how each are executed. For my level of programming, an newbie at that, I've used it interchangably so far without a problem. But I'm beginning to think that there is a difference. I don't want to later have to go back to redo my code down the line. I'm beginning to think that I've been very lucky so far in being able to use it interchangebly and my luck will run out soon.:eek:
View 7 Replies View RelatedHello all!Maybe strange, but I have litterely created a new database from my "old" one. I did this because I thought the size of the database was growing way to fast.What I did was open two instances of Access, one with a new empty database open and one with the other "old" database open. I manually created all tables, queries, forms and code again by selecting everyting in design view, copying it, creating a new table/form/query/module in the empty database and pasting the data. After that I had to set some properties and it was ready to go. After compiling and compacting the new database was just 718kb in size. Empty that is. Then I added a couple of cases, compacted again, added some more, compacted again. (copies of the same data on different records). The same I did with the other "old" database.Both now have 12 clients with 2 cases (totalling 24 cases) every client and every case existing of the same data.The new database is now 1.120KB in size, the old one is 2.096KB. That's almost double. The only real difference between the two that I can think of is that one has a 20KB image tiled on the background of each form (the same image though) plus, of course, in the one database I've been designing, adding, deleting more.But how is it possible that these data sizes are so different? 100KB difference, ok. But almost double the size? How is that possbile and why? Are there things I can do to keep it at bay apart from using the Compacting and Reparing option? Plus: Is this normal?Edit: I emptied them. One is now 812KB in size empty, the other one is 1496 empty. Still... double the size. ?
View 5 Replies View RelatedHi all,
I've just started a new job and one of the things they have got me doing involves getting some extra reports out of some access databases they have for the phone system. I've managed OK so far but I'm stuck on regarding phone diverts.
Basic jist is, when reception is unmanned they put the phone on divert and when they come back they take it off again. Simple and this info is recorded in the log file which is pulled into access each night.
The problem is all of the information is kept in a single table which I run a query on to get the info to look like below. The info is exactly as it is in the table, just that I have limited it to the reception user and where the func is like *54* or *55*:-
DateTimeUserFunc
03/05/200511:40:00Reception*54*200#
03/05/200511:47:00Reception*55*200#
04/05/200511:35:00Reception*55*200#
04/05/200511:40:00Reception*54*200#
04/05/200511:59:00Reception*55*200#
05/05/200509:57:00Reception*54*200#
05/05/200510:08:00Reception*55*200#
05/05/200512:10:00Reception*54*200#
05/05/200512:17:00Reception*55*200#
05/05/200512:19:00Reception*55*200#
The FUNC *54* means put on divert and *55* is off divert. What I need to figure out is the time between each of *54* and *55* and total them for all the data in the table (we have a table for each month). The problem I can see here is that occasionally the receptionist will put in the *55* first which doesn't mean anything so this bit would need to be ignored as only bits where its a *54* followed by a *55*.
Hope this is self explaintory and that someone is able to help. I have attached a CSV fle on the table so you can have a play with it if you can help.
Thanks in advance!
Regards,
Andy Roberts
In access in one field I have one date and in another field I have another date. I want to calculate the number of years.,months and days elapsed between these two date. e.g. suppose in one field I have 30.06.2005 and in other 20.04.2004 then it should show 1 year 2 months and 10 days.
View 14 Replies View RelatedI have a table that contains a significant amount of data. The columns are as follows;
ID, WeekNo, Ref, Cost
What I want a query to do is only show me differences in Cost value where the Ref is the same
For example
ID, WeekNo, Ref, Cost
1, 1, ABC, 123
2, 1, DEF, 456
3, 2, ABC, 123
4, 2, DEF, 789
The query I would like, would only show me the result of Rows 2 & 4 because the value of cost is different between the weeks.
At the moment I have two queries, One that pulls out the last weeks value (WeekNo 1), and the other that pulls out the same information but for the comparison week (WeekNo 2). In the third query I created I linked the two columns from the Ref fields, but could not work the logic to only show me ones where the differences in cost were displayed.
Thanks in advance of the resolution to this. I know it is basic query work, but just have a logical block in getting it to work.
Regards
Hi, I have the following expression in my query;
Worked Hours: DateDiff("h",[Start Time],[Finish Time])-[Breaks]
Start time and finish time appear as hh:mm and breaks is stored as a decimal i.e. 15min is 0.25, 30min is 0.5, 1 hour is 1.
The expression works fine when e.g Start 07:00 Finish 17:00 with breaks as 30min(0.5) gives the correct value of 9.5 hours worked but if the finish time was say 17:30, rather then show the correct value of 10 hours worked, it shows the wrong value of 9.5.
Could someone please tell me where I have one wrong and how to sort the problem, or offer me a different solution.
Thanks
i have a table that has meter readings for copiers on it and dates.
i need to add another column to show the differences in the meter field between itself and the row entered before it (the month before) to find out how many copies were made. i can do this in excel very easily, but cant figure out how i would do it in access
sample:
Date Meter Diff.
06/01/2006 0 0
07/01/2006 1000 1000
08/01/2006 1500 500
we will be entering the date and the meters in every month, but i need to have it calculate the difference between months so i can then calculate overage, if any.
I'm trying to create a database for a Cattery business.
One thing I'd like to include is a query that calculates how long a cat has been staying at the cattery and then calculating the amount owed by the owner.
I don't have a cost field in any of my tables, so I need to create this on every run of the query.
So for example
A Cat resides in the cattery from the 22nd of Feb to the 28th of Feb. So 6 days (not including the day the cat is brought into the cattery) at a cost of 4.95 a day equals 29.7
Heres what I have so far
http://www.tdh1987.co.uk/pics/query.JPG
Bear in mind I'm from the UK, so the date field is in a different format to yours.
Any help will be greatly appreciated.
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 ?
I'm trying to figure out how many day are between now and a date on my form.
I've done a search, but I can't find much that talks about DateDiff().
Here is what I have so far and it's not working. I'm typing this value into a text box on a form.
=DateDiff(Day,Now(),"ASSIGNED_DATE_TIME")
I want the interval to be in days.
Thanks.
Hello,
I,ve made the following date fields in a form: begintime, endtime. I've also made a field Diff that calculates the difference between the two by the code : Diff = Format([begintime] - 1 - ([endtime], "Short Time").
Bij this code you can calculate the difference even over night.
But now I've got to calculate the part of the time someone worked between midnight and 06:00AM if that person indeed worked in those hours.
e.g. someone started at 16:00 end ended at 03:30. The part of time I need = 3:30
e.g. someone started at 01:00 end ended at 11:00. The part of time I need = 5
How can I accomplish this? I hope someone can help me because I'm :confused:
Thnx Remco
I have an inlogform and pasword change form
i have a table "authorized"
userid is the same as pasword if someone wants to try
the problem is: he can't seperate capitals from little letters,
you can try it...
How can I program it that i can make a difference between
capitals and little letters in userid and password
Greetz
veerle