Queries :: Add New Rows Depending On Entered Value
Nov 12, 2013
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows for my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued
0001 01/11/2013 Gloves (Orange) 10
0001 01/11/2013 Hard Hats (Black) 2
0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
View Replies
ADVERTISEMENT
Dec 8, 2014
I have a textbox & button that when entered & pushed - run a query for the top values randomly depending on the value entered into the textbox.
My code is...
strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _
"[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _
"FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between " & Me.StartDate.Value & " And " & Me.EndDate.Value & " ORDER BY Rnd([service_id]) DESC "
It runs like it should but doesn't populate any records...that is until I go into the design view, deselect one of the fields and then reselect the same one - only then does it populate.
Do I need to open the query first and then update/append instead?
View 5 Replies
View Related
Oct 3, 2013
I have a user form in Excel that sends data to a table in Access 97.
(Due to restrictions on system).
I will need to pull reports off this table to send via email to Lotus Notes 8.5
Is there a way I can filter the report depending on the data entered in a column?
aka If a cell in a column has "Cheese" I can run a report showing just the "Cheese" results?
View 2 Replies
View Related
May 28, 2006
Hi Everyone
I have been searching EVERYWHERE, but was unable to locate even the correct wording of my problem, so I decided to see if there is anyone here solve this riddle for me:
I have a simple table with 2 fields
ie :
NAME NUMBER
George 5
John 3
etc...
I have the need to make a report that makes stickers and make 5 stickers with the name George on it and 3 with the name John etc...
I cant even think of a query that would produce 5 rows of George followed by 3 Johns (;})..
The actual problem is more complex than this ( many times ) but this is what is holding me back atm.
Thanks in advance people, and I would like to say that I have learned VERY VERY MUCH from this site alone , and within a month from starting to learn Access for the first time, I have succesfully made a database that is working just fine for invoices and keeping track of inventory....
View 2 Replies
View Related
Nov 7, 2013
I'm trying to update all the rows in a column (column A, PO Number) within a table (iSupplierTable). The value (txtPONbr) is entered by the user on a form (NewPO).
Code:
Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "iSupplierTable"
[code]....
View 9 Replies
View Related
Nov 4, 2013
I have a form where we fill in information for supply of equipment to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
View 11 Replies
View Related
Aug 21, 2014
The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..
I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function
I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.
SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last',
IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-'))
AS IncidentYear,
Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days
FROM (tbl_2c_Acc
LEFT JOIN tbl_Value_Proposition
ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No])
LEFT JOIN tbl_Vehicle_Type
ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No]
GROUP BY IncidentYear;
View 8 Replies
View Related
May 12, 2015
I'm building a database to record which books Pupils at my school have read.I have 3 Tables:
Pupils - ID, Forename, Surname, ID
Books - BookID, BookTitle, Author, Level, Genre
Read - ID, BookID, DateOut
I'd like to create a query that shows the BookTitle of the latest DateOut.
View 9 Replies
View Related
May 20, 2013
I have a database that tracks Construction projects. Each project can have multiple Managers, so I have a many to many relationship set up with tblProjects, tblProjects_Managers and tblManagers. I created frmProjects and sbfrmProjects_Managers. sbfrmProjects_Managers has a combo box control and is continuous. Its record source is tblProjects_Managers, its control source is ManagersID (the foreign key in tblProjects_Managers) and its row source is SELECT tblManagers.ManagersID, tblManagers.Managers FROM tblManagers ORDER BY tblManagers.Managers;. The bound column is 1, the column count is 2, and the widths are 0";4". When adding sbfrmProjects_Managers to frmProjects I made the child-master link with ProjectsID, the PK of tblProjects and the other foreign key in tblProjects_Managers.
I need the Managers to be able to do a search that will return only the projects that they manage. I've tried making a query composed of tblProjects, tblProjects_Managers, and tblManagers. I used the field ProjectsID from tblProjects and ManagerName (with the criteria Like [Manager's Name] & "*") from tblManager. The query worked when run by itself, but when I use it as a filter in a macro to open frmProjects, I get two prompts to enter a value, one that asks for tblManagers.ManagerName and one that asks for Manager's Name. No matter what I do I can't get only the second dialouge box to appear, and regardless I no matter what I enter into the two dialogue boxes my results aren't filtered at all.
View 1 Replies
View Related
Apr 3, 2015
I have 4 columsn, numbers 1 is ModelID, 2 is FitPerSheet, 3s NeedCutOut and 4 is Time: [NeedCutOut]/[FitperSheet]
Thing is, I only enter some numbers into NeedCutOut, and I dont want to see any of the Models that I dont have a NeedCutOut number for.
here is the SQL code just in case. Never know.
Code:
SELECT tblPerSheetTime.ModelID,
tblPerSheetTime.FitPerSheet, tblPerSheetTime.NeedCutOut, [NeedCutOut]/[FitperSheet] AS [Time], *
FROM tblPerSheetTime;
View 9 Replies
View Related
Jul 23, 2014
ACCESS 2007
I've got a table in which some records are associated with a number (numeric field): 691, 692, 693 etc. My parameter query asks for the number I'm looking for (parameter) and should return all records associated with that number or with that number minus one.
Something like this:
enter parameter
find records
in which number = parameter
OR
in which number = (parameter - 1)
How can I do that?
View 4 Replies
View Related
May 30, 2014
I have a database where I need to return only the first record of each key (named "process key") accordingly to a time frame I set.
Consider the table below:
Code:
ProcessContact dateSalesmanClientClient
KeyKey
99904/28/2014 11:39:00SALESMAN 1111CLIENT 1
88804/28/2014 10:13:00SALESMAN 1222CLIENT 2
55504/22/2014 14:29:00SALESMAN 2333CLIENT 3
66604/16/2014 12:14:00SALESMAN 3444CLIENT 4
55504/16/2014 11:47:00SALESMAN 2333CLIENT 3
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
View 9 Replies
View Related
Dec 17, 2013
Is it possible to have the user enter the number of top records to choose in a query?
I know that you can enter a number or percentage in the Top Values property of the query but I would like the user to be able to enter this number each time they run the query since it may be different each time and not always, say, the top 10 records.
View 12 Replies
View Related
Jul 15, 2014
I am looking for a way to either display through a query with an expression or any other alternative that someone may have to display the last 100 records entered maybe based on date entered or something.
At the moment i have a query and report going form start date to end date but would also like a query/report that will just show me the last 100 records entered.
View 10 Replies
View Related
Dec 31, 2014
I have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"
I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.
Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:
This month we did 250 units
Last month we did 300 units
so we did 15% less
"Yes my math is not exact"
They don't need to see last months data but i need a way to query the previous record to compare the data
This is the criteria code i use in the query that "Form B" is based on.
Code:
[Forms]![Form A]![Report_Start_Date]
I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.
How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.
View 3 Replies
View Related
Feb 25, 2014
I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.
So, the best way I saw to go about is to set up an append query and then create a macro that runs it
So my tables have the values FirstName, LastName and isValid (more but keeping it short)
So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it
Field:FirstName
Table:TableOne
AppendTo:[TableTwo].[FirstName]
Criteria:[TableOne].[isValid] = 1
I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.
View 5 Replies
View Related
Jan 8, 2014
I have created a simple login form that is based on User Name and Password.the table contains UserName, First Name, Last Name, Password and User Role(dev, admin...ect.)How can I change who can access different parts of the database?I want all the Development tools only accessable to People who have "dev" as a "User Role".Admin can only access the forms related to data entry and managers can view all forms but not the tables, querie and tools...ect.
View 3 Replies
View Related
Feb 12, 2014
I am trying to copy the parameter value that is entered to a variable so that it can be used to run other queries without typing it in over and over. Once my form is finished it will run 4 queries, and the current setup is that I would have to retype the parameter each time.
View 9 Replies
View Related
Sep 17, 2013
I have an append query that currently looks like this URL...This query automatically adds the machine parameters for a product code and lot number into the running condition log. This is so the user does not have to manually go in and tediously select each machine parameter.
The running condition log also has a date field to specify what day and record number the machine parameter's value was recorded on. When I run the query the appended rows look like this. The product and lot are defined by user parameter and there are actually about 36 machine parameters
Code:
Productstockcode LotNo Day Record Parameter ActualCond
PE-500 130816m71 StockTemp
PE-500 130816m71 Zone 1
PE-500 130816m71 Zone 2
My question is: how do I modify the query to automatically add the date and record number in one shot? It needs to be user defined at the time of the query because this data is not stored anywhere else in the database. For each 36 machine parameters the day and record would be the same.
View 3 Replies
View Related
Jan 18, 2014
Is it possible to limit the type of data that can be entered in a query that has a LIKE criteria?
( Like [Enter Data] & "*")
limit to two digits or any number of digits, or limit to numbers only or letters only. .
View 2 Replies
View Related
May 1, 2013
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));
View 10 Replies
View Related
May 3, 2013
I'm building a Bradford Scoring DB, everything is running smoothly apart from one niggle I would really like to get around and automate.Essentially, I'm hoping to run a difference sum query which is fine when it's on the same row, but the issue I'm having is that I'm trying to calculate this from another row, linked by name. This inturn will workout the difference between the two absence episodes i.e. it's been 80 days since their last absence end.
I did find a method which would view the previous AutoID, but this worked well for the whole table, but not within a query limited to name.The obvious workaround is to allow the team leaders to enter this manually, but this could be a/ open to small errors b/ mildly time consumingI, like most, work with people who want info NOW and with little effort.
View 4 Replies
View Related
Jan 22, 2015
How I might select all the records within a query except for the most recent one? I have an AutoNumber ID field and a date stamp to use , but I can't figure out how to exclude just the biggest record.I am trying to run an update query on the After Insert Event, so that when the user inserts a new record it will mark down similar older records as 'superceded'.
At the moment I can select all the records, and I can select just the largest one, but I can't seem to take one away form the other. Here is the SQL in it's current state:
Code:
UPDATE [Personnel-Qualifications] SET [Personnel-Qualifications].[Superceded?] = True
WHERE ((([Personnel-Qualifications].PersonnelID)=[Forms]![Personnel]![PersonnelID]) AND (([Personnel-Qualifications].QualificationID)=[Forms]![Personnel]![Child21]![qualificationID]));
View 1 Replies
View Related
Nov 13, 2013
I have a table with 4 columns: ID (1,2,3,4,...),Cases, Hours, RIR. In the RIR column I would like to use the following formula.I would like to use a query to Sum the last 12 rows (after index row 12 of course )
View 14 Replies
View Related
Mar 26, 2013
I have the following scenario and do not wish to use a crosstab for various reasons. I am combining three queries into one. The last query has information as follows:
Project Type Rev Budget
12345 Debt 100,000
23456 Impact 50,000
34567 Other 25,000
There are hundreds of these line items. The first two queries hold the project information as well and that would be the field that I would use to join each query. Currently the information above is in line form and I need to put it in column form to coincide with my other data. The final result needs to have the following column headings:
Project, Desc, Exp Budget, Expenses, Debt, Impact, Other, Rev Budget
Please note that the first four headings come from the first two queries so I have this taken care of. how do I change the query to put the Type by line item as a column heading and sum the Rev Budget accordingly without creating a table and doing appends or update queries? Can it be done in the query where I am combining the three queries?
View 2 Replies
View Related
Mar 16, 2007
Hi,
Is it possible to update multiple rows in a Table at one time using Update Query?
Ta
Kasey
View 5 Replies
View Related
