I have a database with sensitive data. I dont want users to change the data, so i used a snapshot query. However when i use this query in another query, the result is an updateable query.
Is there a way around this?
I currently have the sql below...
UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1)
WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
Hello
I have a field called [Hours]
If a user tries to change the [Hours] I need to check if any of these hours have been assigned to staff in another table.
How do I get to run the query and use the value from the result of the query as the Minimum value?
I would like to
run sql and if the value is being entered into the [HOURS] field is less then show a message and do not change allow the value to change.
I am sure this can be done in VB but I am am unsure of the syntax
I hope U can help
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code:
SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE,
(SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL]
WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID
ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
[code]....
I have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
My access is 2013.
Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.
How can I export the list of graduates to multiple excel files, with the filename based on "year"?
I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"
But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.
I should be able to find the answer by searching here... But havent had any luck. Pretty simple question, just don't know the correct syntax.
I have started using a form as a Menu instead of the switchboards. I have a form that one command opens in "Add" mode the other opens same form in "Readonly" mode.
What I cant seem to find is how to code... "If this form is open in "Whatever" mode... THEN...
Example....
Menu has DoCmd.OpenForm stDocName, , , , acFormReadOnly, acWindowNormal, stLinkCriteria
Now on the form it opens I want certain command buttons to only be visible when open in certain modes... So on that forms "onload" event... (I'm guessing "onload" correct me if it should be another event)... I need to have something like
If (Form is open in Readonly) THEN
cmd.whateverButton.visible = False
end if
So, what command gives me the (Form is open in readonly) part?
Thanks
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I have a subform that comes from a table. Depending on who is signed on I need to make some of the records (in the subform) editable, and others readonly (based on some value in the record).
For instance You have several sales managers and when a sales manager signs on to the application we know which state he is from and he can see all records (in the subform) for all states. For the records relating to states other than his state (CA) he can only access them read-only, he can't change them. For the records that have a value of State = "CA" he can change them.
Any ideas (VBA would be ok, and probably necessary).
Thanks
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
Hi,
I have written a subquery that works fine alone, but it returns -1 when with query.
The subquery is supposed to return a total of type currency.
Any help/suggestions will be very much appreciated.
SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;))=True));
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies View RelatedHow to DELETE rows in one table that have a field value that matches a field value from another table? Tried these but sytax errors:
DELETE * FROM tmpBankDebitsMinusJE
WHERE tmpBankDebitsMinusJE.ConcatenateBankDebits IN
(SELECT tmpJournalEntryChangeOrders.Concatenate);
DELETE FROM tmpBankDebitsMinusJE
WHERE ConcatenateBankDebits IN
(SELECT Concatenate IN tmpJournalEntryChangeOrders);
If you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
I have a table that has 3 columns: Unique number, Date, and Results
I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?
I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)
I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.
This is as far as I've gotten with the SQL :
Code:
SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate]
FROM [tblAccounts]
LEFT JOIN
(SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate]
FROM [tblRates]
ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate
ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]
But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)
I need the most recent rate for each of the accounts in the main query
(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)
Hello,
I need to create a query that will seach through a column and only return a result with product codes starting with A.
An example of a product code is AA0000034567.... however there is no realy consistancy. Some start with the AA and some don't. Some have 5 zeros before the numbers start others may only have 1, 2, 3, or 4 zeros before the number.... There are approx 76,000 product codes in total.
My end goal is to create a query to find all the records that have a product code starting with A so that I can eventually delete the AA00000 portion of the code and strip the number back to the point where the 34567 starts.
I figured that by separating out the product codes that start with AA would be a good start.
If any one has any suggestions please let me know.
Peter Vav
I am trying to setup a button that will open a report on click. The catch is that the report name is coming from a query result that is populated into a list box. The way it works the list box will never have more than one result so by default it will always house the name of the report appropriate to a chosen client. I would like to do it this way so that I don't have to clutter up the from with extra buttons all pointing to specific reports.
I found some references to this in this forum, including the example listed below, but I have been unsucessful in making work so I assume that I am entering wrong, entering it the wrong place or maybe the code is incomplete. Unfortunately I am a complete novice to Access VBA so I am still learning how it all works together.
List box name is BillingFormat
The first of 5 reports is named Standard Billing
So in the form I enter the client number and BillingFormat is auto populated with Standard Billing
I tied the button to this code: DoCmd.OpenReport Me.BillingFormat, acPreview
The results I was looking for was for the report named Standard Billing would open. The first way I set it up nothing happened not even any errors. The other way threw an error stating it did not recognize DoCmd as a valid function.
what I am doing wrong?
I have a simple query like below.
Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;
It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.
The error I get is a syntax error the SELECT statements.
I am trying to create a image based off a drop down result. I have had no problem with setting up a image based off a static number. e.g 0456432 in the student id field will bring up students photo in the network share.
What I am having an issue with is the control source will look for a number instead of the name that the drop box displays. this is due to a separate table for the drop box.
what is the expression i need to make in order for the name to appear from the dropbox rather than the source id number?
I am having a problem with 3 pc's attached to our network but at a different campus. They have been complaining that the Snapshot Views of reports they are running are stopping the print queue by asking to load paper type 'bond' to the printer. I have checked the Snapshot Viewer printer settings on thoses machines and indeed they are defaulting to 'paper type - bond'. Setting the paper type to Plain or Unspecified will only print on that type for that instance. Is there anywhere to set the default paper type, i cannot find anything!!
View 5 Replies View RelatedWonder whether anyone can help me?
Each day I produce reports from access and email then out using the snapshot viewer format. The majority of recipients open this find and the report is absolutely fine, however, when other recipients open the report it comes out all slanted and blurred. It almost looks like there snapshot viewer is squashing a landscape report into a portrait view. Does anyone know how to correct this, as its driving me mad trying to find an answer?
thanks:)
I am using Access 2000, Windows XP and Groupwise 6.5. I am trying to email an employee leave report using the SendObject method. I would like to use the SnapShot format. Must I save the report before I send it? I tried acFormatSNP with no success. Thanks for all your help.
View 1 Replies View RelatedI have the following button on a form:
When I press it a mail is being send with a snapshot attached to it
Instead of a snapshot I want it to attach it as a word document.
Here you see the code that is used. I noticed it says snapshot format on a line but I don't know what the name is for a word document.
Code:Private Sub cmdMail_Click()On Error GoTo Err_cmdMail_Click Dim stDocName As String Dim V_EmailAdres As String If Not (IsNull(Me.HauliersEMAIL.Value)) And Me.HauliersEMAIL.Value <> "" Then V_EmailAdres = Me.HauliersEMAIL.Value Else V_EmailAdres = "" End If stDocName = "TransportOrder" DoCmd.SendObject acReport, stDocName, "Snapshot Format", V_EmailAdres, , , "Transport Order BP", ""Exit_cmdMail_Click: Exit SubErr_cmdMail_Click: MsgBox Err.Description Resume Exit_cmdMail_Click End Sub
Thanks in advance
Hello,
I am using a useful function to save data of a form using the snapshot utility. I open the form, add or change data and sent the form to other users via e-mail. The snapshot uses a report. The code to do this is the following:
Me.Refresh
DoCmd.SendObject acReport, "EmailmyForm", "SnapshotFormat(*.snp)", "", "", "", "Action Required", "Please take action on the attached case.", False, ""
I was now thinking if it is possible to save the shapshot report as a file directly into a selected folder on my S drive (ex. S0000abmyfolder. I will need that the file is automatically named for my with the SSN present on the form.
Any idea or help? Thanks.
Is there a way via code in a module to automatically create a snapshot of a report, save it in a specifed folder location in the code and save it as the current date as the file name when a button is clicked.
I'm looking on how to do this as I have many reports to create, move to an intranet folder and then name it to the current date. It will save me lots of time if this can be done automatically.
I am using MS Access 2000 and the folder location will be on my hard drive.
Thanks
Aden