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"));
I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.
Every body has worked on a query using parameter [Forms]![Form Name]![Name of Field of Form] to get the desired result.
It works fine to use for single field. But I could never see this to be used in may fields at one go. For example how about this logic.
Table Name : Document Records, Fields 1st submission letter no 2nd submission letter no. 3 submission letter no.
A query using all three fields based on form field [Text2].
Means if may be letter no. 12 was used for two submissions, now if I put 12 in form filed and run query it does not display any thing. Can some body figure it out and help?
G'day. This is a tough one and I could use some help.
I work for the military and day to day there are people on orders. My finance folks are providing me with an Excel spreadsheet with all the names and start/finish dates of people on orders and I import that into a table.
What I need to do is query out the orders that fall on drill weekends.
An example would be; Joe is on orders from 1-Feb-07 to 15-Feb-07, drill is on the 5-6 of Feb. The queiry would need to "pick" him out as being on orders over that drill date period. Dave is on orders from 10-Feb-07 to 10-Mar-07, for the drill period of 5-6 Feb he would be left out of the results. I would also need to change the drill dates month to month to reflect each drill weekend. Any help is welcome and appriciated, Tim
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 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!
Hi, I've been away from Access for a while but now I'm back in action :) . Maybe I'm rusty from the lack of practice but I think this one will be hard to resolve. So here it is:
I have a form in datasheet presentation and when I double-click on a record, I need to open another form. This I can easly do, now for the hard part, the form I must open has two tabs, one for a specific region and another for the rest of the world. I want to make the sub-form point to the concern record, which I can easily know, from the first form. So, basicly, I need to make a sub-form point to a record from another form.
Currently, I can open the sub-form alone, without going through the parent form, but I can't make it point to the concern record. I can use the "where" clause of the "docmd.openform" command but I would like the form to just be on the concern record and still have access to the entire recordset.
Well, that's about it. I told you it was going to be a hard one, well I still think it's hard :) .
Maybe a little code snippet would help:
Private Sub PEIN_DblClick(Cancel As Integer) Dim MemberID As Long Dim ComputerID As Long
MemberID = Me.MemberID ComputerID = Me.ComputerID
If Me.Region = "ON" Then 'This is where I don't know how to do it 'I tried a "OpenForm" but I can only access the parent form 'And not the sub-form Else
End If End Sub
If you need more infos to help me, please don't hesitate to ask, I'll be monitoring my e-mail box closely :D .
for our company we have an access application we use to keep track of our customer-info.
The databse consists of 3 parts: The (replicated) frontend The databse holding the changing data The database holding the unchanged data (lookup db)
We have the following problem: somewhere in our front-end db is a bug that allows users to change the contents of the lookup db. In our case this can result in a major problem because the users are able to change the city-zip code table. I have tried to figure out where things go wrong but so far no result.
We have picked up the idea of making the lookup db read-only for normal users. This will prevent them from modifying the contents, and will also result in error messages. Hopefully will these error messages point me in the right direction of the bug.
Problem is that the ldb, created when opening the mdb, inheritates the same security settings of the mdb. If we set the mdb to read only, the user gets an error-message stating it can not find the lookup db or that the lookup mdb is locked. This is because the user can not create or modify the ldb. On the other hand, if we set the security to create and modify for the mdb, the user still is able to change the data in our lookup db.
I have never worked with the security in access itself. and I want to try to avoid that. Is there a way of securing the data in our lookup db. So I'm able to figure out what really goes wrong.
I work for a company that has a mainframe application that produces "outgoing" files. They are downloaded to a server and converted to ASCII (all at one time) and then ftp'ed to various clients. We are converting to a client server application and I am in charge of verifying that the mainframe application files (converted) are EXACTLY the same as the server application files. I came up with a process of loading the files to access databases and then with a series of queries and reports produce a list of exactly what doesn't match, which record, what positions and display the two fields that are mismatching.
My problem is that when I try to load the Mainframe file (note that it has already been converted and "eyeball to eyeball" the files are alike) using the "new" / import functions, Access rejects the file because it is greater than 65000 bytes. If I cut the file down to under 65k bytes it loads fine. I can load a 500,000 byte file that was created on the server in the first place.
Any suggestions? I can't change any of the processes that create the files. But I can change copies of the files so that I might be able to get them to load so I can verify that the data inside is the same. I'll let the geeks figure out how to fix the file/record control stuff.
(wouldn't have this problem if I was back on a mainframe!!!!)
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?
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!
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?
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?
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 ? )
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 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 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.
Can i save an SQL statement to an existing Query. what i would like to do is have a crosstab on fields that will be determined at runtime. so i would have the pivot on a field that is chosen at runtime. I can run a sub that will generate the correct SQL, i am just lost on how to save it to an existing query.
the full blown explanation of what i am try to do, is the following. i have a report which is going to use a cross tab. so i need to attach a query to the report. if i assign report.recordsource = SQL i get a message that a crosstab can't be attached to the report. but if i use a saved crosstab and i write report.recordsource = me.myCrosstabQry then it works. don't know why, but it does.
I currently have a query (see code below) showing me a total count of WorkUnits. I would like to exclude WorkUnits if the PossibleCause field is Out of Stock. When I add criteria to Where in the code I keep getting an invalid bracketing issue and I cannot solve it no matter what I have tried. Any advice on how to add the critieria above to this query correctly?
SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals] FROM [Select Distinct [WorkUnit] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;
is there anyway to view records in an access database from an existing database? i have 3 databases that perform basically the same things, but are for different people... i would like to create a database that can report all this information in one spot, instead of creating reports in every database. if this is not possible, i'll probably go the asp.net route, but this seems to be an easier way, if it is possible what do u think? *j
Hi, I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 x x (check boxes) Y ACD 2 x x
Query 2. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 10/8/05 (query performs Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
I've got a query that takes a part number, strips off the un-needed prefixes and suffixes, and gives me just the meat & potatoes of what I need.
I'd like to insert these results into a particular column in an existing table. Say the column name is Part_Number and the table name is CompletedWork. What would the SQL look like for that? I think this is relatively simple but my SQL skills would never be found in the same sentence as 'good'.
TIA for the help. This forum is an incredible source of information.
Wow, seems like this is my favorite place in the world now.
My problem is that I need to append data from one table to another, on the condition that any of the fields in the record have changed since the last time the query ran.
For example,
tblEmployee has the fields 'EmployeeID', 'Name', 'Position', and 'Office'. We are appending to tblEmployeeHistory with the same fields in addition to a field for a date. When executed, the query should append the records where something has changed and attach today's date to them. This enables us to later use another query to call up old information based on the date.
Once again, this is a hypothetical situation and not my actual database. Any solution or comments are welcome.
I am in the process of making a database and basically I have everything done but this saving, loading and adding/removing part.
Would it be possible for me to allow end-users to run queries that they could save, load, add to the existing table or remove from the existing table?
When I say Add/Remove what I mean is could a user of the database run a search of every customer in the state and then remove everyone in County X or on the other hand could they run a search of every customer in the state and then add every customer in a neighboring state.
Kinda like layers and again I have all of the criteria set up I just need the Save, Load and Add/Remove buttons on the form. I should add that I do not want users to save over the main table I want them to save it as there own text file they can upload later.
