hey, i would like to see in the room price column, the total price payed for the room judging by the loyalty(no of nights). So basically i want the loyalty(no of nights X roomprice. At the moment, when i run the query it in the room price it shows just the one night price, but i want to X that by how many nights the person has stayed. I have tryed putting the sum function in the roomprice field and it didnt work.
Thanks.
I'm trying to create a query that will return a calculated field called "PnL".The formula for "PnL" is as follows: PnL = notional*management_fee*NAV*
The "notional" field is a value derived from a table called "Deal_information" (primary key deal_id) and has an associated "management_fee" and "product" field. So obviously finding the product of the notional field and the management_fee field isn't a problem.
However "NAV" field is from another table (VL_information) that contains the historical Net Asset Value of each product. Fields are (VL_id, vl_date, product, NAV).
Therefore for each "notional" record I would like to return the historical PnL.By way of example. Lets say that the notional valuefor a particular record is $100, with a corresponding management fee of 10% (the managment fee is product dependent). Then I would like to find the evolution of PnL by multiplying this record by the evolution of the NAV. This would do so for each notional value.
I have two tables 'LabIssue' and 'Store'.
I created a form for 'LabIssue' in which data will be entered. In the form I want to calculate the 'ChemicalAmount' by multiplying 'Store.Costperunit' with the 'QuantityIssued' which will be entered by the user. ChemicalAmount and QuantityIssued will be stored in 'LabIssue' table. I tried it through the expression builder but im getting #Name? error, did alot of things but couldnt figured out whats the problem.
I am attaching the initial database also for reference.
I've searched and searched to no avail to find the answer, hopefully you can help.
The story is:
At work, our holidays are now taken as hours off. For example...if I take a Monday off, its 8hrs and 5 min and a Monday morning would be 4hrs and 5 min...What I want to do is calculate how many hours I have taken off throughout the year.
The way I was going to do it was:
Create Table called Times,
Fields = Week, Day, Morning, Afternoon, Friday and 1hour all Data Type "Date/Time"
Week = 37hrs, Day = 8hrs 5min, Morning = 4hrs 5min, Afternoon = 4hrs, Friday = 4hrs 40min, and 1hour = 1hr.
Then create another table called Hol Booking
Similar field names Wk, Days, Morn etc. They would be Data Type "Number"
Now in my simple head, I thought I could simply record how many weeks or days off i had off. Then create a query (for example) Week*Wk to calculate the amount of hours I had off.
In simple terms if I had 2 weeks and 2 days off, the query would multiply Week (37) by Wk (2) and Day (8:05) by Days (2), giving a total of 90 hrs and 10 min.
I know there is a datatype mismatch, but is there a way round this.
Thanks for listening
Hi all,
I have a table shown in a form, beside that table a textbox with a button.
How can I multiply the numer entered in that textbox by the the number displayed in the table, and view the result in the same table??
Please help me as soon as possible?
Thanks in advanced and sorry for bothering you.....
Regards,
I have a table called tbl_IE which has a field named frequency.
I want to multiple all of the values in frequency where the ID is 1. I want to display this on the form as the data is inputted and also on the final report.
Names Number1 Number2
Name1 1 2
Name1 2 1
Name1 3 -2
Name1 4 2
Name1 5 5
Name2 1 2
Name2 2 5
Name2 3 2
Name2 4 5
Name2 5 -1
...
For each Name, Number1 will always contain the values from 1 to 5. Number2 can vary, however.
I need constructing a query that, for each name, will look at the values in the Number2 column when Number1 is 1 or 2 and multiply them. So, the result I'm looking for is:
Name1 2
Name2 10
(Since 2 = 2 x 1 and 10 = 2 x 5)
I haven't had much look Googling (most search results involve multiplying two columns together, which isn't exactly what I'm after).
I have a query that calculates elapsed time from TimeIn and TimeOut formatted to hours and minutes. I need be able to mulitply the elapsed time by the hourly wage in order to find the hourly pay.
How do you format the time so that it is compatible to multiply with the wage?
My SQL statement is below. Thanks for your help.
SELECT tTimeCards.ValetFirstName, tTimeCards.ValetLastName, Format([TimeOut]-[TimeIn],"hh:mm") AS HoursWorked, [HoursWorked]*[tValets]![HourlyWage] AS HourlyPay
FROM tValets INNER JOIN tTimeCards ON tValets.ValetID = tTimeCards.ValetID;
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
thank you
-Tim
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.
This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.
What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.
If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)
Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then
Me.CASETIF.Visible = True
Else
Me.CASETIF.Visible = False
End If
End Sub
And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form
Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap
Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria
Thanks
Rahul
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
That is the query.
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
Basically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.
View 2 Replies View RelatedHow can I get some mainform fields' data to be the first entry in a subform? From the mainform, I would like the Head of Household name and date of birth to be carried over to the first entry in the HouseholdMembers subform.
The two forms are tied to separate tables. For each household member I need to be able to enter full legal name, date of birth and some additional pieces of information such as income and source of income.
What I'd like to avoid is making the users enter the Head of Household's name and date of birth twice. Is there a way to carry that information over to the subform?
Thank you, Charlotte
Hey Everyone,
I am stuck trying to figure out this problem. I have a main form "frm_tirelog_600" which has 4 combo boxes "cboleg",
"cbocar", "cbopos", "cboserial" on it. The first 3 combo boxes are used as criteria on 1 of 3 subforms that I have.
The fourth combo box "cboserial" is used for the criteria in a query on another subform that I have called "frm_mount_600_subform",
which is independant from the main form, I hope I have explained that clearly enough. What I need to be able to do is
have the subform fill in 3 of the fields on it "leg", "car", "pos" with the value from the 3 combo boxes on my main form. I
can get it to show in the fields but not write to the table. I have searched the forum and have not been able to locate anything
that would work and really need any assistance with this. Im not real strong in the coding department which is where I
beleive this could be done.
I have attached a copy of my DB which I hope will better explain it. Any help would be greatly appreciated
I have a combo box linked to a look-up table. There are 8 fields in the look-up table but only if one of three is chosen do I want additional combo boxes to become visible. Do I put the code in the "after update" or "on change" event of the combo box and how do I express the code-
If TechniqueCombo="caudal" or "spinal" or "epidural" then
TextNeedleType.Visible=True
(am I close??)
Thanks
Hey,
The database I am working on, I split a while ago to give it some security. Now i'm updating a related form, and i'm finding that if I delete and add fields in the BE, the FE fields (being the fields that I need to insert into the form so the data entered propogates to the DB) are not updated.
How do I update the FE?
Thanks.
I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?
View 8 Replies View RelatedI have two tables - Hours and Employees. Employees table has these fields:
EmpID, and First, and Last name.
Hours table has these fields:
EmpID, Hours, Funding Source, Nature of Work, Pay Period
I need to produce a report or a query that will display these results:
A column with a list of all the employees and all the Nature of Work categories going across with the sum of hours for each Nature of Work. I'm attaching an Excel spreadsheet that shows kind of what I need. Also, these reports need to be produced for each Funding Source and
Pay Period, so something like [Enter Funding Source] and [Enter Pay Period]
My question is that I don't know how to make a report that will just list a column with all the names and count the sum of hours for each Nature of Work.
Hope this is not too confusing, and any help would be greatly appreciated. thank you!
Hi,
I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.
When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.
Hi,
I hava a small dbase in which we store address information. Now you have a visit address, a mailing address and billing adress.
I want to use some buttons which trigger macro's to fill in these "Secondairy" addresses FROM the visit adress.
So a buttun that says: copy billing address from visit address. and then the street, Number, PO box en city will be copied from the visit address to the billing address.
It's probably possible through a macro but I'm having a hard time coming up with the syntax. Or it there an easier way? :confused:
I hope you guys can help me out. THANKS!!
Hello all,
First post :) found some great tips on here. Anyway here is my story and question.
I am new to Access development. I know what I want to do, I just can not find a way of doing it :( .
1) I would like my form to only be editable when an edit button is clicked. I am not sure if I can do this on a form level or if I have to change each individual part. Any ideas?
2) I capture some clients details, title, first name and last name. I then need to merge the above 3 fields into one to create a 'policies in the name of' field. Is this posisble? I have tried using the initial value expression but am having no luck. Any ideas?
Thanks all :)