Query
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
I have varcurve1 varcurve2 varcurve3....
I want to provide a mean (interface?) for user to choose varcurve1 varcurve2 varcurve3 to run above query.
for eample if user choose varcurve1
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
if user choose varcurve2
the query will run
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve2 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code: Private Sub Command3_Click() Dim dbs As Database Dim sqlstr As String Set dbs = CurrentDb Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "") Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
I have two tables. The first contains details of a budget holders money allocation for a given period, and the other tracks their spend on products over that period. How can I generate a query to calculate the total running spend for each user from the "budget spend table" that will be written into the users record in the "budget allocation table".
My aim is to show details of budget allocation, total spend to date and remaining budget for each user in an Order form / report.
Can anyone please advise me on how to do this or suggest another way of doing it. Any help would be greatly appreciated.
I want to be able to have multiple parameter queries, but the user needs to have the option of entering a parameter or not limiting the output at all. How do I let the user choose "all", or what do they type in so that nothing is excluded?
I would like to run a simple select query, where the CRITERIA is based on the user choice. I have a form with a combo box, with a few choices, and a button that will trigger a macro with one query for now. I can store the choice in to a variable e.g. "town", using Microsoft VB code. How can I transfer the variable to the CRITERIA field in the query, so whenever I chose a specific "town", my query will select the records for that specific town. I want to use one query, and I don't want to have to "hard type the criteria in to the cells' query", but I would like to use a variable that can change and will be read it from the user choice through combo box.
I've made a form where the user can fill in a field ID... and than by clicking on a button another form will be opened with the data of that person (with that ID that's filled in). But if the user fill in an ID that is not currently in the database, there will be also shown another form, but than with no data.. Now I want that if the ID is not known, to display a message or something (like "this ID is not known, try again") and not to open the form..
I've used the following code under the button:
Private Sub openenFormulier_Click() On Error GoTo Err_openenFormulier_Click
Dim stDocName As String Dim stLinkCriteria As String
I have Access2003 and a problem creating a simple method of constructing a union query.
I am using a commercial paradox databse that has a habit of creating a new table every time a certain action is performed. There is currently a set of 160 tables with identical fields. The database allows me to view data in these tables individually. I would like to pull the information together, from between 20 and 30 specific tables at any one time, and view the data in one table.
I have linked the tables in a an access database and would go about viewing the data by creating a union query containing each of the tables of interest. Then appending the info in these tables to a new table.
Unfortunately manually adjusting the SQL expression is a bit cumbersome to say the least I am wondering if it possible to select the tables of interest from a List Box on a simple form and have those selected inserted into the union query ?
Alternatively, the table names are actually a field within a different table. Another field within this table actually determines whether they will be of interest. So ideally I could query that table to tell me which tables need to appear in the union query. Again I would like the union query generated automatically as a result of that query.
I am confident, perhaps without good reason , that this must be possible ....
How to proceed and what is the "accepted" version of events.I have created a report, a dynamic method statement actually, and want my user to be able select some data to appear in the report. Report is rptMS01, it is fed from a query and has some fields to automatically populate [Company], [Site], [Postcode] etc. All of the static text is in CanGrow textboxes to sidestep the report height limit. The report is opened by a button on a form which uses a macro to open only for the current site. All OK so far.
My boss would like a section/text box/subreport to select the personnel involved in each method statement and this to appear on the report. So, on clicking the button on the form which would usually open the report directly, some kind of intermediary selection form opens instead with a list of all personnel. User can then select which personnel to involve and on clicking OK this appears in the designated box on the report. A CanGrow/Shrink textbox would be ideal!
Do I need to include this in the current query which feeds the report? I know a listbox can have multiple selections but how would I reference this? How do I keep the other fields as well? Should I create a table with the personnel list?...
I have a form that allows the user to select a record in a combo box. When this selection is made I want a 2nd form to open and give the user a place to enter additional info about that item selected. I have the forms working correctly but now I would really like to add the 2nd form to the first form as a subform but when I try this I get the message"The expressing is typed incorrectly or is too complex to be evaluated..." There are no fields on the first form that link directly to the 2nd form - because the combo box in an unbound combo (the selection in the combo box is what the 2nd form opens base on) I tried to add this as a subform with no parent/child info and I get the message stated.
I was hoping to make the 2nd form not visible until the selection was made in the combo box then make it visible for the info to be entered. Would really like it to be on the same form not as a separate form opening.I believe I just found that the reason for the message. It seems not to be a subform problem but is because the selection in the combo box needs to be made BEFORE the form can open. So now the question is - how can I make this a subform and avoid this error when the main form first opens. I will test the visibility and see if not visible until the selection clears this up.
I have a combo box that needs to only show approved values for any current data input but also be able to show legacy values (that were approved at one time but are no longer approved).How can I only allow the user to select from current approved values but still be able to pull a legacy record and show the legacy value.
Table structure
tblRepairReason ID (Nnumber) Name (Text) ApprovedValue (Yes/No)
The recordsource of the combo box:
Code:
SELECT tblRepairReason.ID, tblRepairReason.Name, tblRepairReason.ApprovedValue FROM tblRepairReason WHERE (((tblRepairReason.ApprovedValue)=True));
I have a few tables, one of which stores a record of changes made to user accounts, and some of those changes are linked to a "multichange" table for when I have made the same change to multiple accounts.
What I am trying to do is select the change_id from my changes table, with a limit of 1 as I only want one result (it will always be the same) but I am using this query and getting a syntax error telling me I have a missing operator.
Code: SQL_SELECT = "SELECT change_id from dbo_user_change where username = '" & username & "' AND change_type = 'new account' LIMIT 1" Set qresults = CurrentDb.OpenRecordset(SQL_SELECT) Do While Not qresults.EOF multichange_id = qresults.Fields(1) qresults.MoveNext Loop qresults.Close
I want to give the user the ability to run a report but display records based on different user defined time frames. So when I run the report it allows (on the Enter Parameter Value prompt) user to enter 30, 45 60 etc (to represent days). The report then looks at all records with a start date in the next x days based on what user enters
For a separate report, I want to be able to run the same report but give user (based on drop down) ability to view all reports or only those from today's date only.
Hi all, i have a 2 fields in a subform named "HRS_ABSENT" & "ABSENCE_REASON" i'm trying to create some code that will display a message if the user inputs any number into the "HRS_ABSENT" field & leaves the "ABSENCE_REASON" field empty. I want to force the user to select a ABSENCE RESON (these are 3 letter codes) from the drop down list, if they enter a number in the HRS ABSENT field. Ive tried the below code but it doesnt do anything :-(
Anybody please help me out? ------------------------------------------------------------------------- Private Sub Form_BeforeUpdate(Cancel As Integer) If HRS_ABSENT = >0 & ABSENCE_REASON = FALSE Then MsgBox "Please select an Absence reason" Cancel = True End If
The database I'm working on stores label images, or better stated, it stores file paths where the label images are stored. These paths are used in reports and forms to view the label images within the database. What I'd like to do is to create a "save" or "email" button that will allow the user to select a location to save just a copy of the image in jpg format. I realize that it is possible to imbed the jpg image into a report and export it as a pdf. What I'd really like to do is find a way to save the image (perhaps by copying it from the path in the database and simply relocating it) and preserve the jpg extension if that is possible.
I am also aware that there are third party applications that will convert pdf reports into jpg files however network restrictions keep me pretty limited in my ability to download third party software.
How to use combo box that drops down the date received from which the user can select todisplay "receipt form" on and after that date. [need to use macro]
I have created a simple database, with 2 x tables. The first is a 'Week Ending' table, with a dateid key and Week Ending field. I have created several week-ending dates in order for a user to input details of work done the previous week. The second table contains the details of the work.
I have a one to many relationship between the dateid in the first table, and a dateid of the work details table.
I created a query based on the work details table, and created a subform from that. In a main form I linked a combo box to allow a user to select a date and the relevant work details appear in the subform. However the Week Ending field only shows one date, not a list of the dates I created.
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline] FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID) WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
i have a form with 2 subforms. when the user selects a record in subform one. the date of that record filters subform 2. in the source query of subform 2 i have this under criteria
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
I have a basic database design, well I think so anyway. It only has two tables:
1. tblClientInfo 2. tblNotes
Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.
I then have two forms:
1. Claims Loss Form 2. tblNotes_DatasheetSub1
So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).
Example of a print out of what it is doing:
John Doe - 123 Easy St - Notes 1 (Most Recent Note) John Doe - 123 Easy St - Notes 2 (Previous Note) John Doe - 123 Easy St - Notes 3 (and so on...)
It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.
How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?
I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.
I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.
The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.
I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.
What I want to do is to view customer information that does not have customer logins.
Hello, I am trying to familiarise myself with MS Access and have the following problem: Lets say I have a Table A with two columns as follows: Name Total Tom 1000 Mary 200 Brigid 150 Niamh 75
My question is how can I - using SQL only - select the nth, ie 3rd or 4th row?
I have spent some time on this and it irks me becasue it is probably very simple. Any help or advice will be gratefully received. Best Regards, Tony
When a Review has a Status = 'Not Accepted' then there should be one or more records in [Results] that indicate what the result of the review was.
I want to structure a query to perform a data integrity check to select any Review ID that satisfies the following criteria: - The Review Status = 'Not Accepted' - There are zero records in [Results] for that ReviewID
I can't figure out how to do it.
Here is an example of the tables where all record are OK...