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?
I am a novice user trying to set up a db for small business and am needing a little direction. Most of the company's sales come from the contracted work for remodeling homes; however a small amount of sales are over the counter sales within their showroom. How do I set up the db to track the following: customer info, contract sales amount & date & payments & balance due, labor costs by employee and sub contractor, materials costs, and net profit from contracted amount vs deductions of costs? Payments made are directly related to contracted sale and not labor or material costs; with the exception of the over the counter sales. My boss currently tracks the above info in an spreedsheet ... please advise. Any help will be greatly appreciated.
Hi I am a teacher using Access 2007 for the first time. I have downloaded and created a Student Info database which contains details of nationality, guardian's contact info, etc. I also have an Attendance Excel Sheet which calculates daily / monthly attendance in its most basic form. How do I link the cells that show the current attendance of each student to the Access db? Can I link it so that it appears on the db forms / tables / individual student page? Please advise. I'd appreciate the coming to me in painfully simple steps. I teach English and Humanities and am not well versed in computer language ... although I enjoy its challenges and use a lot of it for teaching and administratie purposes. Thanking you in advance, Kscape.
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];
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 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'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 am trying to make user input forms for each of the tables in my current database. I have made the forms, but I am required to have the forms come up blank first (so they can create a new record), but allow the user to edit existing records too. How do I do this?
Hello, all. I have created a project in Access 2003 to track a student's progess toward a college degree. I want the user to enter their student id in a text box on a form, look up if that user is present in the student table, and if not show the form to enter their student information.
I can't get the text box to accept user input, and return the student info as a single record on the form. I have a query but I don't know how to set the proprerties to run the query.
I want to put a textbox on a form that will take user input (a postal code) and put it into a table. I'm not sure how to do this. Is it even possible, and if so, any suggestions?
I have a query that prompts the user to display certain/all of the members in my member table. The problem is, using a parameter query in the memberID field only allows the user to send the message to one member, not multiple members. "1 or 2 or 3" or "1,3" obviously don't work. I've tried creating a form with a list box to select the members (which I;ve done) but obviously you need code and stuff to get it to work which I think will be too complicated. Is there a simpiler way or is the form the only thing that will work? Thanks Kris
When I added this field/function to the query, I got #error values and when I clicked on one, I got an error that said:
The expression you entered as a query parameter produced this error:The object doesn't contain the automation object 'Please Enter Year'.'
I assume the problem is that I have the user filter the report by use of the Like() function for [Year] in the query when the report is accessed. How do I tell the DLookup function to search the column as filtered by the Like() function?
i have a database that users log into before they can do anything, after they log in, when they open up a form, i want the username that was used to log in to be displayed and stored in a field. how would i do that?
Here are the relevant fields and tables Name ---------------------- Description USERS --------------------- contains username/password/success/date time for login Login ---------------------- Login form Username ------------------ Username field in 'Login' Password ------------------ Password field in 'Login' EXPOSURE_REPORT --------- Table that contains information for form VV EXPOSURE_REPORT --------- Form REPORTRECIEVEDBY -------- field that needs the current user stored in it
there are 6 or 7 users total, if that's at all relevant, thanks!
I want to put a textbox on a form that will take user input (a zip code) and put it into a table. I'm not sure how to do this. Is it even possible, and if so, any suggestions?
I have a form, and on the form there is a Provider Rate which is a combo box, if the user select a zero rate, then it has the description please enter manual rate in box below. There is another box which the user can enter a manual rate.
how I can limit entry into these boxes, as currently a user can select a rate in the provider rate box and still enter something into the manual rate box. I want it so that if a rate other than zero has been selected in the provider rate box, then they can't enter anything into the manual rate cell.
