How To Select A Single Record To Do Calculations
Sep 28, 2015
I am trying to make calculations at a single record level. I have many products and I want to select only 6 of them, which I just found out that I can do it by their SupplierID. then I have a query that finds the amount of pcs that I have for each product. The aim is to determine the final weight of this 6 products. So to start I need to find the weight of each individual product by multiplying the number of pieces (from the query) by the weight of a single piece. Obviously each product weights differently, and at the end I need to add all the weights of the individual products to make the total weight of the 6 products.
don't know how to approach the problem of selecting a single record. And also I don't know what would be best to do it in a query, a form or a report.
View Replies
ADVERTISEMENT
Jun 4, 2007
I have 2 calculations in a single query.
1st - ProdCost: [TBLinvqty]*67
2nd - Gross: [TBLinvamt]-[ProdCost]-[TBLfrtamt]-[TBLocamt]
The query runs and works fine but it keeps prompting me for ProdCost but if I hit ok it runs fine.
How do I get it to quit prompting me for Prodcost
BTW, Prodcost is not a field in a table its only the 1st calculation in this query
View 8 Replies
View Related
Feb 12, 2014
I am designing a database in Access 2010 to handle customer discounts. The problem I am facing is that the discount can vary by customer. One could be a straight percentage based on certain sales totals, even the percentages can vary by customer, and some customer discounts are tiered. I was hoping there was a way to handle all through one query, because there are so many variations, it would require several queries if I separate by each calculation available.
Is there a way to set up a table listing the customer and their corresponding calculation expression then running a query using that table and the sales data table to do all the calculations. Or is there another way to handle this level of complexity in Access? The end result I would like is one data set with each customer and their calculated discount.
View 1 Replies
View Related
Sep 8, 2014
Any Single line of code available?
View 2 Replies
View Related
May 15, 2014
I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
View 9 Replies
View Related
Sep 6, 2005
Here is my problem.
I have to select a single row multiple or certain time ( the number given in a field in that row).
Here is what i need.
Table : Car
-------------------
ID NamePrQTY
01Volvo999910
now i need to select this single row 10 ( given in QTY field) time.
Like this
SlID NamePrQTY
101Volvo999910
201Volvo999910
………………………………………….
…………………………………………..
1001Volvo999910
Can anyone help me.
Thanks
Sadi
View 2 Replies
View Related
Oct 15, 2013
I have inherited a database that is used to track equipment that comes in for repair. One of the fields in this database is a memo field that lists the parts that are replaced on a piece of equipment. It has been requested that I leave this field as it is. However, I would like to regulate the data that is entered into to some extent. I need to ensure that there is a comma between every part number listed (there is another group that wants data from this database and they want to be able to separate this field out into individual fields). Is there a way to have Access look at the data in a memo field, find all the periods or ampersands, and replace them with commas?
The other thought I had was to provide the person filling in the database with a multi-select list box that lists all the available part numbers for a given piece of equipment. That person could select as many as she wants and when she was done, the database would take those values, put a comma between each one, and paste them in the memo field. Is that possible? What I have tried isn't working. I tried to get it to loop through the selected items and put them in the memo field, but I just end up with the last value selected instead of all of the items selected.
I don't know what to try next, Either the person entering the data is going to be upset because she has to type every part number in a different field, or the people receiving the data are going to be upset because there isn't a comma between each number.
View 5 Replies
View Related
Dec 21, 2007
Hi,
I have a query that selects values from a table and I'm further creating a letter based on this query. The problem i'm having is that if the query pulls 3 rows as result then it creates 3 separate letters. Basically this is how the output of query looks:
(refer attachment)
Is there any way to write this query so that I can get output as below:
Col1 Col2 Col3
10 20 10
Please advise.
View 7 Replies
View Related
Nov 13, 2011
I created a subform that displays the records of a select query after selecting the desired data from a single combo box (for instructors name) and then using an OpenQuery macro attached to a button.
Only 1 record is displayed in the subform each time I hit the button. If I look at the query return there is no change. However, if I close the query and select a new instructors name, although I only get one record in the subform, the query when opened shows all the correct records.
What is wrong with my macro/subform?
View 8 Replies
View Related
May 6, 2014
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
View 1 Replies
View Related
Sep 28, 2015
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
View 14 Replies
View Related
Jan 12, 2015
I have a database where I need to record student data, specifically marks and absenteeism. The marks are recorded each term as a running total. I have a final mark which needs to be equal to the 4th term mark.
I also need to record the number of days absent each term with a total at the end of the year.
My problem is that the term 4 mark is a in the previous record and the total days absent needs to be the total of the previous 4 records (term 1 - 4).
I using a create table query and an update query but that was a no go.
View 7 Replies
View Related
Oct 4, 2013
I have partially done it using PrevRecVal module I found on the web.
I created what I need to do in Access in Excel first, the problem doing this in Access (for me) is I need to refer to the results in the previous record, PrevRecVal worked but I need to finish if possible.
The main report holds client policy data of which InvestAmount is used to start the calculation of the sub report, the data entered to run the calculations on the subreport would be
QtrDate
CurrentUnitPrice
The Excel spreadsheet is attached ....
View 10 Replies
View Related
Feb 22, 2007
Hi,
On our main menu table, I only want to be able to add one record.
On the form that I have linked to this table, should you press the tab key to go through all the items / buttons, it opens a blank record screen. However, I do not want this. Any ideas?
Thanks in advance
Lee
View 2 Replies
View Related
Sep 5, 2005
I have 2 tables and a query to show the details of both tables on a form for an attendance database. The idea is that whenever someone is absent they have a record created.
tbl[Staff] has the following fields StaffID,Name,Team,Job Title
tbl[Absence] has the following fields StaffID, abStartDate,abEndDate,abIssue
I can set up the query to find the member of staff by name but I want to be able to use this form to add new records to the Absence table for each individual. eg I use the query to search for "Joe Bloggs" and the form shows the details from staff table and then use the form to add details to the Absence table.
Any help would be greatly appreciated
View 2 Replies
View Related
Sep 5, 2005
I have 2 tables and a query to show the details of both tables on a form for an attendance database. The idea is that whenever someone is absent they have a record created.
tbl[Staff] has the following fields StaffID,Name,Team,Job Title
tbl[Absence] has the following fields StaffID, abStartDate,abEndDate,abIssue
I can set up the query to find the member of staff by name but I want to be able to use this form to add new records to the Absence table for each individual. eg I use the query to search for "Joe Bloggs" and the form shows the details from staff table and then use the form to add details to the Absence table.
Any help would be greatly appreciated
View 2 Replies
View Related
May 18, 2005
Hi,
I am trying to run an append query, but instead of appending the whole table, I would like to only append a single record.
I have an append query, and it works like a charm. I can also get it to work with prompting the user for a parameter (in my case LeaseId which is a primary key). When the user is prompted and enters the LeaseId it only appends the single matching record. Works like a charm.
However, I don't want to prompt the user for the paramater. I want the user to generate the LeaseId based on the selections in two combo boxes.
So far the I do get the right LeaseId, but I have no idea how to actually pass the LeaseId to the query. I've tried the following, but I know it's completely wrong.
stDocName = "approveLease"
DoCmd.OpenQuery stDocName, acNormal, acEdit, "[LeaseId]=" & Me.buildingCombo.Column(0)
Any ideas on how to actually accomplish this?
View 12 Replies
View Related
Aug 13, 2005
I need to set up a preferences type form. What's the best way to accomplish this. Can I store this data in memory as a variable?
Here's what I need:
Customer Name, CustomerID.
I have been reluctant to put it in a table because this is the only data that I need.
Plx lead me down the best path.
View 3 Replies
View Related
Mar 30, 2006
Is it possible to create text box for search for records if I use SINGLE FORM?
i try but without effect :(
pls see my database (I want to use for record "Title")
Thanks
View 2 Replies
View Related
Feb 17, 2005
hello. can i refer to a single record in a detail section, and not all of them? for example if i use something like this (after update in field1):
if me.field1 = 0 then
me.field2.enabled = false
any single field1 equal to zero will disable ALL of the field2's. i would like this to work within each record independently.
View 2 Replies
View Related
Aug 19, 2014
I've done some looking into how to validate in Access and I found how to do it for an entire field but I was wondering if it's possible to validate a single record in a field.
View 6 Replies
View Related
Jan 7, 2015
I have a form NoWorkOrder, i have attached a command button to Print Report. The report is named No Work Order. When I'm viewing that a record in Form View and click the command button i want only that record to print out onto the report.
I'm using Ms Access 2010.
View 14 Replies
View Related
Jul 20, 2015
So I have a list of jobs and each job is subject to change from the original plan.
Each change needs to be documented and dated. So what I want is a form where the site can be selected and a note written. When this is saved, a record of the current time would also be needed.
The form aspect of this seems fairly straight forward to me. I just cant visualise where the 'notes' will be stored.
View 2 Replies
View Related
Dec 1, 2014
I would like to be able to place a button on my invoice form that will allow me to e-mail the individual invoice to the person being billed (the e-mail address is included on the form). I created a macro that works to send a PDF file of all of the records and I am having trouble figuring out how to send just the current record. I would also like to have the e-mail address automatically populated based on the e-mail address on the current record.
View 3 Replies
View Related
Sep 16, 2013
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
View 8 Replies
View Related
Aug 15, 2007
Hi all. Using Access 2003 I am trying to figure out how to print and collate a report and an existing pdf file that is opened based on a field in the record. I figure that it probably has to do with looping through the records but I am not very good at that.
So I have a form with a sub form that opens records based on a combo box. I then have a report (a checklist sheet) that prints 1 page per record. I would like to print a pdf file associated to that record before it prints the next record.
Here are the names of the objects being used:
tblMoveDoc
frmMoveDoc
subfrmMoveDoc
rptCheckList
Any help would be appreciated.
Thank you,
Matthew
View 4 Replies
View Related
