We're running an Access 2010 web database.
As expected when we published to Sharepoint additional fields were added to the Access tables [App Created By] and [App Modified by]. We want to use these fields for Audit information however they are always empty. How should they be populated?
I have a table in my Access 2010 Database with few date/time fields. I have set different formats like Short Time, Medium Time etc. But when I link this table to Sharepoint list, all the date/time fields have been modified to their default format (General Date).
Is there a way we can keep the formats as such even after linked to Sharepoint list? If not, is there a workaround?
I have three Queries and I need to export three queries into one workbook but different worksheet,
Currently I am using ExportWithFormatting , but the result came out is three different workbook .
Is there any way I can export to one workbook ?
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I hid a query and now need to modify the fields, how can I unhide the Query in 2010?
View 1 Replies View RelatedI'm trying to write a query to get a count of Volunteers under a certain Job Code for a given year - problem comes in that a single Volunteer may record hours multiple times under a Job Code in a given Fiscal Year. I can't seem to get "Unique Values" to work. From my research it looks like I need a two-part query but (as a newbie) I'm not quite sure how to write that. I have two queries, one that works and one that doesn't.
This one counts total amount of hours volunteered under a given Job Code, it works:
Code:
SELECT tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup, Sum(tblHoursWorked.HoursWorked) AS [Sum Of HoursWorked]
FROM tblHoursWorked
GROUP BY tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup;
HAVING (((tblHoursWorked.FiscalYear)=[Enter Year:]));
This one attempts to count number of Volunteers that worked under each Job Code in a given year - it instead counts number of entries under that job code. What I think I need to do is count unique instances of the NamesIDFK, but I can't seem to get that to work.
Code:
SELECT DISTINCT tblHoursWorked.[JobCodeLookup]
FROM tblHoursWorked
GROUP BY tblHoursWorked.[JobCodeLookup];
1) correcting my second query and 2) putting them into one query so I can use them in a report.
Apologies if this sounds vague , but I have developed a number of reports for an sql database which have selection criteria in certain forms. nothing too sophisticated . The company wants to increase the availability of the reports and the selection forms. Unfortunately Access on everyones PC is not part of the plan.
The concensus in the IT team is that Sharepoint is the route.
Does anyone know what would be required (in the way of programs , expertise etc) to convert or adapt an access database so that the forms and subsequent reports would be available sharepont.
I have had a dig around and I am not sure that our IT boys have got the full picture, I think that Sharepoint is dependent on access but I am not certain.
I have a query that has multiple IDs and different information in numerous fields. For example:
ID Field1 Field2 Field3
1 x
1 m
1 b
I need to find a way to concatenate the data so that it shows the information like this:
ID Field1 Field2 Field3
1 x m b
I'm working in Access 2010.
Yes I know this isn't the best way to set up a database, but I'm trying to make fixes to an already existing database that I can't go back and change the way it is set up. I can only find work arounds to accomplish what I need.
I would like to create a full outer join in Access2010 between two tables with many to many relationship.
So I have tblServer and tblApplication and there is also the ServerID-ApplicationID table that connects these tables.
I found online many examples but all of them where for two tables with one-to-many relationship.
and I have several queres using the same table. I was trying to add 2 columns and fields to my 1 querie. I do not use program much but I have it for my cattle. SO I was clicking around. Went to Table and went to add the field.I changed my Primary Key field then realized I should not have done that.I changed it back to "ID" and "Number" and added my fields. THEN I went to the one querie and added my columns and fields.However NOW the one querie I messed with has 30,888 rows as it just repeats the 20 or so rows over and over .The other queries do not and they use some of the same data "ROWS" from the table
View 1 Replies View RelatedI have a date field with dates stored as MM/DD/YY, ie.10/13/09 and I want to use an UPDATE statement to change the year from 09 to 2009 but keep the 10/13/. The issue I'm encountering is when I use a wildcard in the SET portion it takes that as literal and updates with the wildcard. ie. Set Date = '*2009' WHERE Date LIKE '*09' updates the column with *2009 as the entry and not 10/13/2009. How can I update my field so I end up with 10/13/2009, not *2009?
View 4 Replies View RelatedI have played with this problem for 3 days and have come close but not quite solved it. My problem, I have several drivers delivering several orders, the orders are named 101, 102 and so on lets say to 150. Due to locations of the drivers, some deliver more orders then others. I want to be able to create a report that looks like
"Driver #1 101 - 106"
"Driver #2 107 - 110"
Driver 1 delivered 6 orders. Driver #2 delivered 4 orders and so on.
I have tried the 'count" which gives me the number of orders per driver but having trouble figure out the design of the calculation in the query.
I am using Access 2010. How do I prevent the object typed into the textbox on a queryform being written to the table. The result from the name typed into the textbox on my query form correctly produces the result from the query, and my macros then produce the correct report, which I can either print or close due to the controls in the heading of the report. However, when I view the table, the name typed (only) has been inserted into the correct field as a new record in the table. Is there a macro I can add (I assume to an event in the query form) to prevent this happening?
View 8 Replies View RelatedI am trying to execute the query below with multiple left joins because of the data I am trying to get back. The weird thing is sometimes it work and then sometimes it gives me a join expression error. It seems that access strangely removes brackets around the ON clauses. However even when I put those brackets back in this query it isn't working. why this query isn't running or why the brackets disappear in Access 2010.
SELECT Patients.[First Name] & " " & Patients.[Surname] AS Fullname
, [Clinic Patient].[MYMOP ID]
, NZ(MYMOPs.[MYMOP Date], [Clinic Patient].[First Appointment]) AS [MYMOP1 Date]
, MYMOPs.Completed AS [MYMOP1 Completed]
[code]....
I use access 2010. I have a form that allows the user to assign products to operations. Each operation can produce multiple products and each product can be produced at multiple operations. I manage this many-to-many relationship through the use of a third table that stores primary keys for associated products / operations.
tblOperation
tblProducts
tblOperationProducts
The query im trying to design will populate a listbox on the form that contains products which are available to be added to a given operation. In addition to the listbox there is also a combobox that allows the user to select the operation they want to add products to. As the user selects an operation, the query behind the listbox should return records for all products except the ones that have already been assigned to the operation selected in the combobox.
So far I've been able to make the listbox return all products always or return products that haven't been assigned to any operation at all, but I'm struggling to formulate the logic in my mind on how to show only products that have no association (through the tblOperationProducts table) with the operation selected in the forms combobox.
This is the query I have right now, it's not working:
Code:
Select tblProducts.ProductID
From tblProducts
EXCEPT
SELECT tblProducts.ProductID
FROM tblOperation RIGHT JOIN (tblProducts RIGHT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblOperation.OperationID = tblOperationProductMM.OperationIDMM
WHERE (((tblOperation.OperationID)=[Forms]![frmOperationProducts]![cboOperation]));
date formula that I was using in access 2007 doesn't seem to be working in 2010.
The formula that I had was
Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1)
And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3 ,31).
The calculation allowed me to count holiday hours taken between 1st April and 31st March. The problem is that it was working up till 31st march but is not now showing holidays taken since 1st April.
I have a MS Access 2010 application when the User opens form CONTACTS Form
◦User finds single record to be used CONTACTID is identifier to be used for selection
◦User clicks button to open form frm_MAIL_MERGE
◦Frm_MAIL_MERGE has a drop down combo box that reads the folder location where the application resides and displays all .DOTM files (that is all template files) and one more combo box which contains the CONTACTID.
◦User selects single .dotm file for merge
◦Frm_MAIL_MERGE has either drop down to select CONTACTID or UNBOUND data field for user to type in CONTACTID number
◦User enters CONTACTID to be used for the mail merge
◦User selects SUBMIT
◦Application merges selected .dotm file with the information in table CONTACTS for the selected CONTACTID
◦Merged document is saved on the user Desktop as xxx.docx
Is it possible to have a value in an excel 2010 cell be used to update an access 2010 db?
For example, if in a spreadsheet "test" cell A2=3 then in a db "test" a column "value" is 3. However, if the value in the spreadsheet changes so does the db.
I wish to work in Access 2010 with a subset of my Outlook 2010 contacts (all desktop- no network barriers), so I am exploring the Desktop Contacts DB found in Microsoft's templates. It passes contact data back and forth using the standard macros, but I wish to add more fields from Outlook. It seems I need to find the code associated with the macro. I believe I can solve the problem using a Linked table, but am wondering if there is another solution?
Calendar synchronization is another issue. Linking gets me to the Archive Calendars, but not the Current one in Outlook which is what I want.
I am currently using INNER JOIN and GROUP BY to narrow down the RowSource of a combo box based on the value selected in the active combo box where the code is located "onClick".
The value being selected is a StoreID, This ID is matched against the AssetRegister to find all group names, The GroupID and GroupName are then retrieved from the AssetGroup table, then finally the list is grouped on the Group ID and Group Name.
I have tested this query in SQL Server Management Studio and it works without any issues, however when I use it as an inline query to adjust the row source of the combo box it returns no values.
Here is the SQL query:
Code:
SELECT AssetGroup.ID, AssetGroup.GroupName
FROM AssetGroup
INNER JOIN
(SELECT AssetRegister.AssetGroup, AssetRegister.Store
FROM AssetRegister
WHERE AssetRegister.Store=7) AS ar ON (ar.AssetGroup = AssetGroup.ID)
INNER JOIN Store ON Store.ID = ar.Store
GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName
And here is the inline query applied to the RowSource:
Code:
AssetGroup.RowSource = "SELECT AssetGroup.ID, AssetGroup.GroupName FROM AssetGroup INNER JOIN " _
& "(SELECT AssetRegister.AssetGroup, AssetRegister.Store FROM AssetRegister WHERE AssetRegister.Store=" & StoreID & ") AS ar ON (ar.AssetGroup = AssetGroup.ID) " _
& "INNER JOIN Store ON Store.ID = ar.Store GROUP BY AssetGroup.ID, AssetGroup.GroupName ORDER BY AssetGroup.GroupName"
AssetGroup.Requery
Access 2010. This has worked before but I don't understand why this is not working for me now. When i select an item in a combo box in a form and click on a button to run a query with the results, the query is blank. If I run the query alone, it prompts for an item, I can type it in and it works. I have even tried the DoCmd on the combo box but still same results. Attached is a dummy down DB. If you run the query, it will prompt, select Paper or Rock, see results. But run the form, the drop down will not show the results. What am I missing?
View 3 Replies View RelatedI have just upgraded from Access 2003 to 2010. Now I'm trying to relearn some of the small things I used to be able to. In 2003, I could just have a table highlighted and select "Insert, Query". It would then open a query design with that table. Is this possible to do this in 2010? Or do you have to open query design then add the table manually?
View 2 Replies View RelatedI am trying to update and insert records with SQL statements. Below my code:
Select Case FirstGRV
Case "Y"
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = date(), SellingPriceDateTo = #" & Me!Text3 & "#" & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateFrom =#01-01-1900#;"
[Code] ....
My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
View 3 Replies View RelatedAccess to Web. Is it possible to publish access to web without SharePoint?
View 4 Replies View RelatedI would like to publish my access file in sharepoint but I know very little about sharepoint and how to attain a free or testing or trial account. Essentially I would like others to access and edit the file on the web simultaneously.
All youtube sites speaks to how to get started using sharepoint but always assuming that you already have access but this is not my case. I need to know how to acquire sharepoint.
On my computer there is a sharepoint application which I installed but it seems to have no purpose.