Hi all.
Stumbled onto this forum during a google search.. looks like a great forum.
I have a question.
this thread http://www.access-programmers.co.uk/forums/showthread.php?t=124689
goes into a simple function to group by the name and add their total.. however what i need to do is that plus have a user input of the date.
eg of the table
Date Title Invoice
02/02/07 ABC123 11.00
02/02/07 ABC123 12.50
02/02/07 DEC123 11.50
03/02/07 ABC123 10.50
What i need is a paramater query to be able to work to give me the total of the groups..
Whenever i try to join my Parameter query with my sum query it gets messed up..
heres the code... what am i doing wrong?..
What i want displayed after they input the date is the group by and sum total of the parameter query..
SELECT RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST, Sum(RawData_tbl.Invoice) AS SumOfInvoice
FROM RawData_tbl
GROUP BY RawData_tbl.[Title 2], RawData_tbl.[Estimated Value], RawData_tbl.[Date In], RawData_tbl.Quote, RawData_tbl.GST
HAVING (((RawData_tbl.[Date In]) Between [Please Type the first day of the month] And [ Please insert the last day of the month]))
ORDER BY RawData_tbl.[Title 2];
Okay my access skills are low at best and I'm trying to build a database for work so hopefully this isn't to stupid of a question.
I'm trying to creat a query that will allow the user to type in a work type and have it generate the all records containing that work type. The way the data is in there to get it to generate just a specific work type without the input I have to use Like "*newacct*" for example. Is there any way to do this since there are more then one work types for some records?
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
I'm using an Excel spreadsheet that is importing external data from an access database in which I've got a field where the name of a person checking materials out is entered. it is currently set up, and I cannot change it, as a free form field. So folks enter information in a variety of ways.
For example, Larry Martin might be entered as "Larry Martin" or "Martin, Larry" or "larry.martin@somewhere.com". I'm trying to run a query that would look in that field for any entry with the string I enter, such as "Martin."
I've tried setting the criteria like this:
Like "*" & [Which Last Name] & "*"
However, when I try and run the query I get a message telling me the system is expecting two parameters.
Does anyone have any idea what I'm doing wrong? I've been banging my head against this for awhile now and am thoroughly stumped! All assistance, as usual, is greatly appreciated!
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code: SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error UNION SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
I have a cross tab query that takes input from the user. The code is as under:
PARAMETERS [START_MONTH] DateTime, [END_MONTH] DateTime; TRANSFORM Round(Sum(([A_GAS_m3]+[NA_GAS_m3])/1000000),3) AS GAS_MMSCM SELECT maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block FROM maindata RIGHT JOIN PRODUCTION ON maindata.field_block=PRODUCTION.FIELD_BLOCK WHERE ((PRODUCTION.MONTH) Between Format([START_MONTH],"dd-mm-yyyy") And Format([END_MONTH],"dd-mm-yyyy"))) GROUP BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block ORDER BY maindata.on_off, maindata.state, maindata.OPERATOR, maindata.field_block PIVOT PRODUCTION.ACTIVITY;
I am facing 3 problems with the above code:
1. Access prompts to enter the start month and end month even when I click the save button. 2. When I run the query it prompts to enter the start month and end month twice. 3. I have generated a report based on the above query. When I run the report it keeps on prompting for start month and end month and does not give the final result.
I have a crosstab query. users need to run the query for any week of a year. It is always a YTD query. I have used this process on many select queries. Will a crosstab query not support this simple method? See attached screen shots.
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
I am building a form in access and I am trying to find a way where user input isn't possible in the associated textbox when "No" from on option box is selected.
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
I am having trouble extracting top 5 vendor with the sumOfSpend (actual Field name is Spend which is dis-aggregated) for each of 5 groups. Each group has thousands of transactions for dozens of vendors. The Table is designed as follow.
Table1: GroupName : Text VendorName : Text Spend : Currency.
Hello, I have 4 input field boxes in a form: "basic hours", "overtime hours", "holiday hours" and "total hours" i would like it so that when the user enters number(s) into the other field(s) the total field will calculate (add togther the other fields). So for example if the user puts "10" in the "basic hours" input field and "5" in the overtime hours" field then the "toal field" will display the number "15". i would like it so that the "total hours" field calculates the total progressively after each number in the other fields is input (ie a running total is displayed). The user will always fill in basic hours but 1 or more of the other fields can be left blank. I think i need to do some VB on the after update procedure for each field entry but not sure what the code is?, any help would be brilliant.
I can not get my Access Report to give me a total number of pages by group. I've been able to have it give the correct page number per group but not the total number of pages in each group. I've looked at the threads and it appears that in order to get this, you must do a 2 part pass. 1st to get the page number and then to get the total papers per group.
I've used the code supplied and when I do a print preview, I get the message that it can not find the control, Me!ctlGrpPages. This is the control I created and placed in my page footer section of the report. I've also seen numerous references to the report, "Employee Sales by Country" in the Northwind database. I downloaded the database but could not find this report in the database.
Access 2003. Job booking data base with up to group of 10 different users. At the end of the month, I need to count the total amount each user has checked a particular check box and then automatically calculate the total of the whole group.I have only a basic working knowledge of Access 2003....
I have two tables: COSTING and PRICING, both with the following fields: product name French packaging transportation ddp
I want to create a query so that from a text box in the pricing form the user could enter a desired "margin" (ie. 10%) and the query would replace everything in the pricing table with the data from the costing table multiplied times the margin entered in the text box.
how can i accomplish this???
so far i can update the pricing table with the costing data with a action (update) query, but im still missing how to integrate the user "margin" input. At the same time i have the problem that the query limits the update only to the products that are ALREADY in the pricing table. So it doesn't copy ALL the products from the Costing table.
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
I am currently looking into some of the security aspects of Access, as I am still not yet sure how i'll be designing a new database program, (whether to use Delphi to link to the database or run the database through Access itself) however, I was wanting to look into the security aspect of Access itself as part of my education and decision making process. Now I have deduced (correctly?) that some of the user/group access rights are based on the NT/2k/XP User/Group permissions etc, yet I was wondering if there was a little more to it than that.
In some ways I would like the application (through access or otherwise) to initialize with a logon prompt, that would ask for username and password. The database would internally?? retain the password information for security rights. I'm not sure if i'm thinking this through correctly, so i decided to come here for a few pointers in the right direction. I'd like to set up internal to the database a set of groups and users with different levels of security. I don't want them based on the XP user account passwords and such, but just an internal database login. This would allow, to some degree, a specific master group to add / remove users from the database, as well as control user access rights to the database internally. More or less, I want the database itself to be self-contained with all security and access rights, instead of basing them off of some external NT based security information. So to my actual questions:
Is this possible? Where would I start looking to set this up?
Hi, I have put my database in a package using MS Access extension but the user and Group Perssion is not included to the package. Does some one know how to include the permissions too.
This is a database that uses user-level security setup in Access 2003 but it is being run on 2010. An update to the security system is in the cards in the near months, but right now I need to add one more user and I am having an issue with that.
I can add the new user in the user and group accounts form and I can add him to any group except for one. Unfortunately that is the group that he needs to belong to. You can see in the attached image the new user (jschiff) and the group I want to add him to. There already are some other users in that group. What could prevent me from adding this new user to that group?
I'm reasonably new to all this, and have read many posts and got most of my answers from reading the posts here! THANKS.
However I need some help with the following: I have a form that allows someone to input information about products being booked in. However sometimes we have the same product from the same supplier with the same invoice number etc.. but they have individual serial numbers. So the scenario is I have ten items being booked in, they are all the same product from the same supplier but they have individual serial numbers. I have created a do while loop where I can duplicate all the information for each product, but I need it to pop up a box asking the user for the serial number each time it goes through the loop. He is what I have so far.
Dim MyQuantity As Integer Dim TempSerial As String Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("BarcodesDB") MyQuantity = 1 Do While MyQuantity <= Me.Quantity 'insert into table using either a recordset or Append Query rs.AddNew rs!Supplier = Me.Supplier.Value rs!Date = Me.Date.Value rs!Invoice_Number = Me.Invoice_Number.Value rs!Pcode = Me.Pcode.Value rs!Item = Me.Item.Value rs!Quantity = 1 rs!Serial_Number = Me.Serial_Number.Value 'Get this value from user each loop rs.Update MyQuantity = MyQuantity + 1 Loop rs.Close Set rs = Nothing
I have to change a group membership of one user in an multi user access database. As I see its no possibility to change it in the backend (in X.ldb file) nor in frontend.
