We have a database that we are sharing on a network How can I set up a table (or other method) that will automatically log when the database was last updated and who did it (the username). I would like to be able to run a report based on the modification dates of a particular user. Thanks in advance!
View RepliesI have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
I am a self taught Access user and I am attempting to modify an existing report. The existing report is based of a query with six fields. I am trying to add 2 or 3 additional fields to this report. If the other fields are in another query, can I add them to this existing report or do I need to create a new report all over again?
View 4 Replies View RelatedHi all.
Once upon a time I stumbled upon an example form or selection or forms that I merged that allowed the admin user to add and modify user accounts that were set up using the User-Level Security wizard. The form showed the user accounts and group there each were in both of which could be changed or added to as weel as passwords changed or set without going through the full toolbar selection method.
I have since deleted this from my computer in one of my random clean ups but have since been asked for it again. Does anybody have any knowledge of this form or where I might be able to find it?
Cheers
Hi all;
I know this thread has probably already been done but I've tried searching and nothing I've found seems to be what I'm looking for. What I want to do is incorporate an automatic Date update to a table, every time an item is updated in that table. I've tried creating a new field "DateLastModified" and puttin =Now() in as the default value, but this does not input a date into the field. Any suggestions on how I can get this to work?
Hi all,
In Northwind DB i made a crosstab query which summarize no of categories that every customer buy his products from.
It works well in case of I summarizing all orders without any criteria.
If i set a criteria such as Order Date is greater than 1/1/1998, the form displays the customers who made orders in this interval only.
Now, How can i make it displays all customers and summarize the categories of customers who didn't make orders in this interval by zeros rather than deleting them.
I’m trying to modify an existing SQL query that calculates actual working days between start date in tblECN and end date in tblECNDetail to do the same between start date in tblECNDetail and end date in tblECNDetail (same table).
Where I’ve gotten into a bind is with the joins of the two tables that are needed in the first query but not in the second as both fields come from the same query.
In the current working query I join about tblECN.ECNID and tblECNDetail.ECNID. In the new query I don’t think I need to create these joins at all as BOMEntryStart and BOMEntryEnd both come from the same table (tblECNDetail). I’ve bolded all references to the table no longer required for join but I don’t know which join statements I can be rid of. When trying to save I get an error in the FROM statement if I leave as is. If I get rid of the join statement I get an error
working query
SELECT [tblECN].[ECNID], [tblECN].[RelDate], [tblECNDetail].[PendDate], DateDiff("d",[tblECN].[RelDate],[tblECNDetail].[PendDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[PendDate],2)>5,5-Weekday([tblECNDetail].[PendDate],2),0)+IIf(Weekday([tblECN].[RelDate],2)=6,1,0)-IIf(Weekday([tblECN].[RelDate],2)>Weekday([tblECNDetail].[PendDate],2),2,0)+IIf([tblECN].[RelDate]=[tblECNDetail].[PendDate],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL
FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
WHERE (((tblHoliday.Holiday) Between [tblECN].[RelDate] And [tblECNDetail].[PendDate]))) AS Query4
GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE ((([tblECNDetail].[PendDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));
Not working query
SELECT [tblECN].[ECNID], [tblECNDetail]., [tblECNDetail].[BOMEntryEnd], DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5,5-Weekday([tblECNDetail].[BOMEntryEnd],2),0)+IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)-IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)+IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays, Query5.HCOUNT
FROM ([b]tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL
FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
WHERE (((tblHoliday.Holiday) Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]))) AS Query4
GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE ((([tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));
Any help is greatly approciated.
I would like to do two things
1.) pink arrow // I've changed the titel in a diagram with vba ... is there also a methode to move the position of the titel with vba ( left-center or rigth ? )
Private Sub Form_Current()
' KostenHinweis im Diagram
Me!Dia_WS.Object.ChartTitle.Text = _
"Preis: " & Me.[Roh-Preis] & "€/kg " & _
" Mindestbestand =" & Round(Me.MindestBestand * Me.[Roh-Preis], 0) & "€ ; " & _
" Maximalbestand =" & Round(Me.MindestBestand_Max * Me.[Roh-Preis], 0) & "€"
'Me!Dia_WS.Object.ChartTitle.Left ???
End Sub
2.) red arrow // maybe its because of the titel placement.I would like to change the size of a diagram with VBA code to have the diagram fit nicely to a given space best thing would be if there would be a possibility to resize the diagram in the given space
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
I have been modifying an existing report that was formatting and printing fine but since I modified it (adding some text boxes and moving some controls around), it is now adding a blank page.
For example, If the report was normally 2 pages long with page numbers, it is inserting a blank page without page number between page 1 and page 2......
I am dabbling in updating an existing database.Currently our staff enter data via a form and I have this set up using the below script to automatically complete the "Due date" field with a date 5 working days ahead of the current date, saving them from having to manually enter this date each time.
Code:
Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
[code].....
This then updates the value on the relevant form object using the default value "PlusWorkdays(Date(),5)" to give a date 5 working days ahead. the problem I face is that due to our business process at 15:30 each day our due date needs to change to 6 working days from todays date rather than 5. I was wondering if there is a simple way I can modify the script to add an extra working day when the local time hits a specified point (15:30 in this case), unfortunately this is a bit outside my abilities currently!
I am trying to trap the database error "View cannot be updated because modification affects multiple base tables" in the Form OnError event to display a user-friendly message instead of the above.The above can happen in the scenario of inserting/updating several fields of different tables at one time, likewise what I am facing now is the scenario of the user copying an entire record and pasting it.
I tried
Case 4405
MsgBox "Can not update the record. it is related to base tables"
Response = acDataErrContinue
But it doesn't work. How can I know the case number and solve all the scenarios of multiple insert/update attempts?Also, are there any better solution than this? I first tried to detach the SQL statements out of the views and make it one select statement so that I can normally multiple update/insert(as this problem happens with views only) but since CTE is not supported in Access , I failed to do so.
Help!!!
I am setting up a database with 'open' and 'close' dates of an event for each record/ID. I want to be able to sort/query my results into the next event (i.e. open or close) as it will be happening so that I can plan for each event, but I am not sure how to go about doing this. I have tried various queries but because the information is linked to one ID it does not want to duplicate the information in the record. I can set up separate queries for the 'open' and 'close' dates but I can't seem to be able to tie it together
=>I have 2 queries, but I really need it to be at least in one report in date order each record appearing twice ...once at the open date, once at the closing date.
anyone....please!!!!!!!
:) thank u
I've been trying to figure this out, but it looks like a no go.
I wanted to give my users the ability to create/modify queries, but NOT create/modify tables.
Does anyone know if this is possible in Access 2002?
Thanks,
Earl
I have a query that records a status update, and the date it was made by an employee etc. The table has a secondary key related to ... lets say a project table.
E.g.
Status Id; EmployeeID;ProjectsID;Status;Date
I want to calculate the average amount of days it takes for a status change occur.
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
View 1 Replies View RelatedI have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
Hiya-
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
Many thanks, Jules.
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
Is there a way in this program to create a list of dates between 2 dates?
i.e I have Arrival Date and Departure Date. Is there a function or expression that will list all the dates on and between?
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible???
Ex.
Joe March 3 to March 8
Mary March 4 to March 9
John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
Anyone.....
Hi
I want to add Hours to a date value.
For example the date value=05/04/2005 18:12:35
I want to add three hours to that date value so the new date value will be 05/04/2005 21:12:35. Is there operatior to add dates.