Reports :: Totals From Multiple Combo Box Fields
Sep 4, 2014
I'm fairly new to Access but I've been tasked to re-jig an old databse running on Access 2003. So far the database is doing more or less what I want it to but I need to generate a report showing totals from multiple fields in combo boxes. As an example, I have a combo box for a job booking-in form which requires the type of task to be selected e.g Video, photography, editing. The report needs to show the total booking numbers for each tasks (combo field) at the end of the month. I've managed to create reports for totals of a single field in the combo box but I'm struggling in achieving the multiple totals.
View Replies
ADVERTISEMENT
Jul 2, 2013
I have a combo box named Session which comprises of am and pm. I have a number of fields that relate to this combo box.
E.G. Monday am Sales £1000
Monday pm Sales £ 2000
I want to create a total Sales of the am and pm to derive a total of £3000.i.e. Monday Sales is £3000 which is a combination of the am and pm sessions.
View 7 Replies
View Related
Jul 29, 2013
i have a table with health facilities (A,B,C,D) . each health facility has data from several months (Jan, Feb, Mar etc). the table has 2 fields (New Patients) and (Cumulative Patients) . Cumulative Patients is a total of New Patients for current month plus the total patients for the previous month. In the Facility Footer of the report if i create text boxes with data =Sum([New Patients]) and =([Cumulative Patients]) works well.
However in the Report Footer =Sum([New Patients]) works but =Sum([Cumulative Patients]) totals everything. if i try =([Cumulative Patients]).
View 8 Replies
View Related
Dec 28, 2014
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
View 2 Replies
View Related
Feb 8, 2008
Have been searching all morning & can't find solution (technique must be haywire)
Have created a combo box query with 3 fields, id-code-description
Objective is to select Code from combo box and populate Description field from the value in the combo box into another table
Have tried all sorts of techniques, none of which have worked
I am sure this has been answered a million times, but I just can't find it
Any help appreciated
View 7 Replies
View Related
Jan 14, 2014
I am using mail merge to produce reports for lift inspections and it is all going well apart from the fields that are multi value do not quite transfer. For instance the "inspections completed" field has a list of the months Jan-Dec and therefore a visit may have been completed in Jan, Mar & Jul for example.In word the first two letter of the last month are transferred across followed by Chinese symbols.
View 1 Replies
View Related
Dec 2, 2005
Ok... big time newbie/hack here. I have a tiny bit of programming experience from waaay back with UNIX and C. Have basic database experience and understanding but no VB experience. So I appologize ahead of time if this seems really simple. I'm working on "fixing" a small access database/program for my wife's work. Very simple design.
I have a company table and a customer table. I have customer form and a company form. They may have several customers at the same company. So my design has them starting on the Customer form. They begin by filling in the Customer's Name and then they get to the Company Name combo box. They should select the Company Name from this box. If it is a new company and is not in the list they should click on the "New Company" button beside the Company Name field. Then the Company Form opens and they can add a new Company along with Company address and phone numbers etc. Then they close the Add Company Form and then can select the newly added Company form the Company Name combo box (by the way, the only way I could get this box to update with the new company name was to use a macro that only runs the Requery statement for the On Enter event... it works... I hope this is ok).
Anyway, this is where my problem arises. I need the selection of the Company Name to also pull over the address, phone number, fax number, etc. info from the Company table and complete it on the Customer form/table now. How do I do that? I think I need to do this with the After Update event with some VB code... but I have no idea how to actually pass this data. The property for my combo box is as follows:
Control Source........CompanyName
Row/Source Type.....Table/Query
Row Source......SELECT zMASCompanies.CompanyName FROM zMASCompanies;
The field doesn't have to be a combo box (if this is not the right way to do it)... I just need them to select a company name and have it fill in about 20 or 25 fields from the company table/form into the customer table/form.
I did try searching the forum and found a few things but nothing I found was very clear (especially since I am not sure I am even heading down the right path). Any assistance would be greatly appreciated!!!
Any help please????
Thanks,
Jeff
View 7 Replies
View Related
Feb 18, 2015
Is there a way to have say five different reports that give out information and at the bottom the totals. I would like to take each of those separate reports to create one report with just the totals.
View 3 Replies
View Related
Jan 21, 2015
I have a report with the fields
Firstname_
Surname_
[field1]
[field2]
etc etc (they're not called field1/2 but they're irreverent at present.
On the database people can be entered multiple times so I may have
Bob Smith
Jimmy Carter
Bob Smith
Jackson Smith
Tim Jones
Jackson Smith
Bob Holmes
Now I now with the group function I could group by the firstname_ field but the results would group like the following
Bob Smith
Bob Smith
Bob Holmes
Jackson Smith
Jackson Smith
Jimmy Carter
Tim Jones
And if I do it by surname, well I am sure you get the drift.
So how can I sort so I would get the following
Bob Smith
Bob Smith
Bob Holmes
etc etc.....
View 2 Replies
View Related
Jul 11, 2005
Hi all,
Im trying to do the following query work.
I have a form, with combos , text fields which are filled in with parameters.
The query I want to make is complex.
For example we shall use field1, field2, combo1 , combo2 for explanation.
I want the field1 param AND the field2 AND combo1 AND combo2 to be evaluated for a result. At the same time, I want the user to fill in only field1 and combo1 or like that and the result to be right.
Is there any possibility to make them all work at the same time, individually, or in combinations ?
Sounds too Complicated ?
I ve managed to make it work only by filling in individually the fields or combos and have the right results. The other ways didnt.
Desperately need your helpppp. :rolleyes:
THNK U
View 1 Replies
View Related
Sep 24, 2014
I have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria
First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")
To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"
On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.
View 4 Replies
View Related
Jul 21, 2015
Working on a report that displays multiple pie charts. Each chart is based on a different query. I cannot pull a single query for all charts due to the criteria for each conflicting with each other. Each query is filtered by Fiscal Year based on what I input into my Fiscal Year Filter form. The command button on the FY Filter Form opens the report, set TempVar to the FY field (the criteria for each query), and closed the FY Filter Form. This works as I want it to.
The problem: I have additional fields I want to show up on the form such as number of completed students (WINGED). This number is based on yet another query where all completed students are counted [WingedCount]. I have tried to write an expression to an unbound field that points at this [WingedCount] field but it does not work. Then, I changed the report's source as the query with the [WingedCount] field. This does work, however this is where I run into an issue
I open my FY Filter Form and type in my criteria and select the open command button. However, now I am asked for the criteria again for each chart on the report.
I need a way to input the criteria only once and have all charts populate as well as my count field.
I have attached a jpeg of my current report and will upload jpegs of the needed output following this post.
View 6 Replies
View Related
Jul 24, 2015
I have a report based on a query that I put inside a form. What I want to do is create 4 comboboxes from which users can choose options that will filter the report without leaving the form or opening the report in a separate window. Also, I want to make the filters dependent on each other, meaning if a select option A on combobox 1, the options i will select on combobox 2 will only be based on the earlier filter on combobox 1 ...
So far I'm able to do at least 2 of the comboboxes to filter the report, but they aren't based on which one was used to filter the report first. So if i filter the records by choosing option A on combobox 1, combobox 2 will still filter every record.
here's my 2 codes so far:
Private Sub cboLocation_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboLocation) Then
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
[code]....
the other 2 comboboxes, I can't apply the same code cause the enter parameter dialog keeps popping out.
View 3 Replies
View Related
Nov 5, 2014
I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.
Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.
I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.
So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".
So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...
Got relationships between:
"contact name" fields in both "table2" and "table3"
"project number" fields in both "table1" and table2"
In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?
Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????
View 6 Replies
View Related
Mar 21, 2013
[URL] ....I am trying to dynamically search multiple fields from a combo box on a form that includes a subform. I am using code from the above referenced link within this forum.
It works without the "setFocus" for the Listbox except it jumps to a record after the very first character is entered instead of narrowing down as characters are typed. If I leave in the "setFocus" I get run-time error 2110.
View 14 Replies
View Related
Mar 12, 2013
Access 2010
I have a form bound to a table which has 20 (name)fields and 1 date per record. A user fills in this form first to indicate which people are present this day. I know it would be better to use 1 name per record but that would in this case not work since the form must show all names for that day before saving and closing.
I have a different form where I use 4 combo boxes and a date field. The values that can be selected in the combo boxes should come from the 20 names on the first form and with the same date as the other form. So only 4 people of the 20 indicated as present on that day can be selected to have performed some task.
I know how to select with a combo box from different records, but how would I do it from different fields in the same record? And then also for a particular date?
View 1 Replies
View Related
May 3, 2012
I have created a combo box search for my form based on three categories, 'Student Name', 'Nationality', 'Age' using the wizard. When I click on my combo box in form view, I see 'Alex', 'UK', '19' and 'Stephen', 'Sweden', '22' in the dropdown list, but I do not see the headings 'Student Name', 'Nationality', 'Age' as the first item on the list.
View 1 Replies
View Related
Feb 21, 2011
I am using Access 2007 and have reviewed many blogs and looked at the Northwind db to try to solve this problem. I would like to create a form that functions in the same manner as the "Order Details" form in the Northwind db. Specifically, I want to create a form that will allow me to input a value and have the form get data from another table and populate the table I need populated.
I would like to populate a table "Outside", field names are Key, Name, term, Value, Date.
The table that contains the data is named "CV" and the filed names are Key, Name.
In the form, I would like to enter a value for Key into a combo box, and have it put the value for Key and for Name into the form, and allow me to enter whatever I want to in the Term, Value and Date field. When I tab out, I would like all this data to be in the table "Outside.
View 8 Replies
View Related
May 26, 2013
I'm trying to create a form using a combo box to populate multiple fields and tables.
I've created a text field to display the added information using this format:
=Comboboxname.Column(x)
in the text box control source field, and this works for display purposes.However, I need it to populate this data into a field on a table.
For example:
My combo box looks up data that has 2 columns, Part Number and Description.
The control source for the combo box is "Part Number". And that populates the part number in the "Main" table no problem.
The text box I created using the above format in the control source populates the field in the form, but not the "Main" table.
Is there a way for the other (description) field to also populate the "Main" table as well?
View 5 Replies
View Related
Mar 15, 2006
If I have a report that is returning numberous records from a table, how do i set it up to total the values in each field (AT THE END)
I have a series of query's that can calculate the totals I am looking for but cant find a way to place them at the end of the report. If I place them in the header or footer it puts them on every page....
Any thoughts how to just have them show up at the END of the report?
THanks in Advance
View 2 Replies
View Related
Apr 30, 2005
I need some help with a report that has running totals of both positive and negative values. The report needs to have a subtotal of the positive values only, then the negative values only. The negative and positive values appear all in the same list. The running sum property over group/over all only adds all the values together which is of no use. Is there any easy way to do this?
Example Report snippet:
(Ignore all the dots - its the only way I could get everything to line up)
# Code...Cleaner...Current..+1Week
1..12....J. Smith........$12.20.....$4.50
2..15....R. Anderson...$10.00.....$2.30
3..18.....E. Jones.....-$14.00.....$1.50
4..19....A. Rob...........$3.50....-$2.50
TOTALS: (+)............$25.70.....$8.30
.............(-)...........($14.00)...($2.50)
-------------------------------------
Balance.................$11.70.....$5.80
View 1 Replies
View Related
Mar 14, 2014
I have several reports on access 2010 that lists items and at the bottom in the report footer gives me a total for the columns. Is there any way that those totals can be put at the top of the columns such that the person reviewing the reports can see the totals first, then scroll down to see the details?
View 3 Replies
View Related
May 8, 2013
I have a table with:
Invoice Number | Customer Name | Item | Item Cost | Invoice Date | Paid | Date Paid
Example of data:
AK001 | A Brown | Blue Car |1000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Red Car |2000 | 1/4/2013 | Yes | 20/4/2013
AK001 | A Brown | Yellow Car |500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown | Black Car |1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith | Blue Car |1000 | 12/4/2013 | Yes | 25/4/2013
I want to create a report from this table that outputs as:
Invoice Number | Customer Name | Total Price | Invoice Date | Paid | Date Paid
Example of report from Example Data:
AK001 | A Brown |3500 | 1/4/2013 | Yes | 20/4/2013
AK002 | A Brown |1000 | 7/4/2013 | Yes | 20/4/2013
AK003 | B Smith |1000 | 12/4/2013 | Yes | 25/4/2013
Is there an easy way to do this.. or will I need to make a new linked table with the invoice number as a lookup?
View 2 Replies
View Related
Feb 2, 2005
Hi all,
I have a registration form where a person can select if they will attend any or all of 3 events. I want to create a report that will say x registered for event 1, y for event 2, and z for event 3.
I have 3 queries which seperate the people by whether they are attending event 1,2,3. In these queries I have CountofID field so that I can sum it.
When i create a query that Sums the CountofID field for each one, it multiplies the values. I played around unsuccessfully with crosstab queries, but to no avail.
Any help would be greatly appreciated.
View 1 Replies
View Related
Aug 29, 2005
I have monthly reports developed from various query's and what I want to do is have a single report that I can display my monthly totals from each monthly report without developing a whole new report. I want to use the existing totals from the monthlies and have them all on one report. Can anyone help me?
Jaxfire
View 8 Replies
View Related
Jul 30, 2015
I am trying to get my totals from my subreports, and dividing the number by 2 or 4, and put the new number on the main report.
for Operator/Trainer Productivity, I used:
=([rptEmployEvaluationOperator subreport].[Report]![OPTotal]+[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal])/2
for Auditor/Trainer Productivity, I used:
=([rptEmployEvaluationAuditor1 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor2 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor3 subreport].[Report]![OPTotal]+[EmployEvaluationAuditorTrainer subreport].[Report]![OPTotal])/4
These both work if there are values in all totals subreports. When one of them might not have a total, I get an error message.
I tried to use :
=IIf([Orders].[Report].[HasData], [Orders].[Report].[txtOrderValue], 0)
=IIf([Orders].[Report].[HasData], Nz([Orders].[Report].[txtOrderValue], 0), 0)
=iif([rptEmployEvaluationOperator subreport].[Report].[HasData],([rptEmployEvaluationOperator subreport].[Report].[OPTotal]+=iif([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report].[OPTotal])/2
and the same idea for the second one. I tried both methods and did not work. I am not sure what I am missing.
View 10 Replies
View Related
