I am creating a Sales History Report. It displays products sold by customer by Sales Rep. The groupings are Sales Rep, Customer, and Product. I am hiding the detail section which contains invoice line item records. So the report shows the product subtotal by Company. The problem I have is I need to see the product subtotals in the Sales Rep and Report Total sections also. So the report would look something like this:
Sales Rep A
Customer A
Product A
Product B
Product C
Customer B
Product A
Product B
Product C
Sales Rep A Total
Product A
Product B
Product C
Sales Rep B
Customer R
Product A
Product B
Product C
Customer S
Product A
Product B
Product C
Sales Rep B Total
Product A
Product B
Product C
Report Total
Product A
Product B
Product C
Is there a way to accomplish this without doing sub-reports?
I am looking to do some calculations in a form that can be output to a report.
Basically within the form i have 4 controls as well as the others
Tables Benches Chairs Other Equipment
These controls are bound to a table.
The first 3 controls need to have have fixed costs 2.00, 1.50 and 1.00 respectively. The last needs to be manual input as it can be different dependant on the items in the memo box.
I then need each one to calculate separately to give me a sub total then add all together to give a total
This total needs output to a report for sending out a bill.
I currently have the 4 controls with 4 unbound textboxes beside them for the subtotals:
TablesTotal BenchesTotal ChairsTotal OtherTotal
4 unbound textboxes for the fixed amount and manual input amount (maybe this is where i'm going wrong)
TableCost BenchCost ChairCost OtherCost
and then one more unbound textbox for the total:
TotalTotal
Do i need to create a table for the fixed costs? If so whats the best way to set this up. What should i be binding textboxes too if anything?
Folks, i have a report that i designed to capture some detailed information and my users say its too detailed and they cant use it that way,so they want it scaled down to that very brief format as in the attached pdf file.I conquer with them but i cant seem get beyond what i did as every table seems to be a sub table of the another thus the cause of grouping and sectioning. Has any one done something like this before!!, or are there any ideas,tricks, examples i would look at as to meet this requirement!!!
Evey Idea is highly appreciated. Thanks in Advance Kea
I have a Movie db which consists of one table with columns such as Actors, Genre, Rating, etc. I currently have numerous buttons which are used to search each specific data type. For example, I can search Genre, or Year, but not both. Is there a way that I can create a form, where various items are selected and from there, a search is conducted based on the various selections? I also want to be sure that if a single field is blank, that it just ignores the field and doesn't search for "blank" fields.
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
I want to automate the distribution of individuals Vacation Balance and Usage via email. The data is being pulled from 2 tables related by an Employee ID#. tbl-Employees provides the Header Info for each employee and tbl-VacLog provides the detailed Usage data.
I want the output to appear as follows in the body of the email:
Name Start Date Vac. Bal TotVacToEOY Personal Bal. John Doe 1/1/99 120 160 8
Usage Date Hours Reason Code 1/1/13 8 V 2/15/13 12 V 3/6/13 8 V
I've got a handle on creating the email and sending but where I'm having trouble is in making the link between the two tables with the Employee ID# and printing the corresponding detail data with Parent Record.
Code below...
Option Compare Database Sub SendMails() Dim DB As Database Dim RecordSetA As DAO.Recordset Dim RecordSetB As DAO.Recordset Dim TotalRecordsA As Integer
Not sure if the question belongs in queries or reports. I've a list of product codes that we produce daily, weekly, monthly. Some repeat several times within a week or month. I want to list them in order by cost. I can do that, but when they repeat, I wanted to combine them into one subtotal, then get sorted by cost with the other product codes.
Right now, I'm dumping the table into Excel and doing it manually. I sometimes have 7 lines of the same product code. I insert a line and subtotal them by their product code. Then, I delete the data leaving the subtotal line. I then can sort them with the other subtotal and other once product codes.
:-[ I'm hoping this is an easy answer. I'm not good with Access. It's turned out to more of a trial and error thing for me.
ProductA would also have a minimum inventory level of 6 in another table. So the parameter I would like to create would sum all the different lots of ProductA and compare it to the MinLevel entry.
Hi there - I've looked at quite a few posts and although some of them seem to start of heading in the right direction there doesn't appear to be anything that quite hits the spot for what I'm looking for.
I'm trying to create a query that creates the following (see spreadsheet as typing it out didn't help.)
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers] but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
Hi there, I am fairly new to access and am having trouble doing what I need to do. I have a table that contains 2 number fields, 14 yes/no fields, and one text field. I want to be able to display the total number of yes's per field, and the total count of each individual value for the number and text fields.
I was able to make individual queries that count all of these, however I'm unsure how I can make a query that will do it all together, or how to put them all together. I may be doing this wrong, or there may be a better way of doing this.
Any help would be greatly appreciated! Thanks very much!
is there a way that i can get a total from a number on a form and a number on a sub-form (I want to add the numbers from the form and sub-form and have the total appear in a text box on the form)
I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.
Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.
Below are the 12 scenarios:
1.) Reduces 75% at Age 70
2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000
3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.
4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.
5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%
6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.
7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.
8.) Terminates on the first of the month following the 65th birthday.
9.) Terminates Age 70
10.) Terminates on date of retirement or 1st or month following 70th birthday
I'm trying to get a total which runs off of a query in a listbox (disguised as a textbox) to always show 1 decimal place. Right now, it'll show one decimal place only if the number is rational. If the number is whole, it doesn't show any.
E.g. For 8.5, it'll show 8.5 For 8, it'll show 8, not 8.0
folks, I have two simple tables in a database. One Table is the entry table and the other is the totals. I then created a Form and a subform from the two tables. What I would like is to maintain running totals in the subform as entries are made in the entry table. Attached is the Database. Please help.
I am creating a football dream team database with two tables:
Team table - Team ID, Team Name, Player 1, Player 2, Player 3, Player 4, Player 5, Player 6, Player 7, Player 8, Player 9, Player 10, Player 11 and Points Total,
All the player columns are lookup functions which are linked to player table
players table - Player ID, PLayer Name, Position, Club, Value, Points
My problem is i cannot figure out how to calculate the points total in the team table from inputting the points into the player table.
I also would like to know how to create a 'total' column by adding together other columns in a table.
I have a table. The table stores a workers name and information such as Primary Station Verified. The Primary Station Verified is a yes/no checkbox. It also has the dates that each worker is verified.
I want to do a query that will show me the total number of people and out of that total number of people how many are verified. Then I will turn that data into a graph.
How do I do the Query to show me (example) There are 50 people and (example) 25 of them are verified? I know how to do the query to show all of the people and who are verified but I just want the numbers.
I'm needing help with a totals query. I have a table with a Department field, a charge field, and a charge code field.
I'm running a query that first groups by the department, then uses the max function to give me the highest charge within that department. For the third field of the query, I want to return the charge code that aligns with the highest charge, unfortunately none of the general totals seem to work and I can't figure out an expression to work either
I have a table where 4 different people enter their amount of work sheets they complete each day via a form. Each person could have a different total each day. Example:
AJ=5 CH=10 MH=7 CA=3
Each person will have a entry for each work day of the month.
I am creating a query that needs to show each persons total for a month. I know how to get the month but how can I total each persons page count all together and then separately? Example:
All four combined total page count
AJ Page count individually CH page count individually MH page count individually Ca page count individually
I'm trying to make totals in a query, but instead of using all, I need to leave one out, how to do that? for example the table looks like: A 2 3 B 4 6 C 2 5 D 3 5 E 2 3 How can I leave row 1 out? So that the query will look like: 11 19