Subtotals In Queries

Aug 2, 2006

Hi,

I'm trying to produce a subtotal in a query to compare it to another field to determine whether to include the record or not.

For example, in my table I'd have,

ProductA 3(units) Lot1
ProductA 4(units) Lot2
ProductB 2(units) Lot3

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.

Any ideas?

View Replies


ADVERTISEMENT

Queries :: Exporting Query To Pivot Table Remove Subtotals?

Nov 19, 2013

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?

View 1 Replies View Related

Subtotals

Feb 15, 2008

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.

Thanks

Laura

View 2 Replies View Related

Subtotals In Reports

Nov 15, 2005

I have a report that has the following categories:
Parent--Unit--Month--Spend--Expected--Rate of Expected

Now i have it summing the spend by Parent. I would like it to sum by Parent by month, is this at all possible?

View 1 Replies View Related

Complicated Subtotals Query

Apr 5, 2006

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 hope that's clear - any help appreciated.

Cheers,

Mark

View 1 Replies View Related

Detailed Subtotals & Totals

Dec 1, 2005

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?

View 3 Replies View Related

Calculating Subtotals And Totals

Apr 17, 2007

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?

Hope this makes sense.

Thanks in advance

View 2 Replies View Related

Referencing Subtotals Fields On Mainform From Subform

Mar 9, 2005

Hi,

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?

Thanks very much!

View 3 Replies View Related

Parameter Query Using Dates Leading To Subtotals

Nov 4, 2011

I have a list of coded transactions in column A, Amounts in B and Dates in C.

I want to show subtotal for all amounts with the same codes in D

I can do this simply without the date parameters, but I need to give timeframes.

When I add the [From] and [Until] questions it then shows the amounts next to each date even though the date column is hidden.

I'm aiming for the subtotal of amounts for same codes between given dates:

Example
ABB 100 1/6/11
ABB 100 6/6/11
ABB 150 1/8/11
ABC 50 1/7/11
ABR 50 2/8/11
ABJ 50 1/5/11

If I enter 1/6/11 and 30/6/11 I would expect ABB to show 200 total as 150 is after that date and so not counted.

View 2 Replies View Related

Modules & VBA :: Exporting Query To Pivot Table Remove Subtotals

Nov 19, 2013

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?

View 1 Replies View Related

Queries :: Unable To Open Or Design Queries From Older Access Files

Jan 1, 2014

Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions.
The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :

" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....

View 6 Replies View Related

Queries :: Running Multiple Queries To 1 Excel File With Different Tabs For Each Query

Jul 18, 2013

I'm using Access 2003 and excel 2003.

We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.

I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:

Trasfer Type Export
Spreadsheet Type Excel 8-10
Table Name (query Name)
FIle Name (FIle location)
Has field names No
Range Blank
----
This does seem to work and puts the data on a new tab on the specified workbook.

However I have a few questions:

1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.

2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.

3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?

View 1 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related

Queries :: Crosstab Queries - Export Command Ask To Enter Date Parameter Twice

Feb 24, 2015

I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?

Code:
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryEXPORT"

[Code] .....

View 9 Replies View Related

Queries :: Joining 3 Queries And Displaying Results In Separate Columns

Jul 31, 2013

I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).

My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)

Using this SQL:

Code:

SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION

[code]...

Which returns a table that looks like this:

Final Equipment, Duration

Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004

And so on.What I need the table to look like is this

Final Equipment, 3 Mth, 6 Mth, Historical

Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004

And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.

View 6 Replies View Related

Queries :: MS Access 2010 - Export Queries Into One Workbook But Different Worksheet

Jul 16, 2013

I have three Queries and I need to export three queries into one workbook but different worksheet,

Currently I am using ExportWithFormatting , but the result came out is three different workbook .

Is there any way I can export to one workbook ?

View 3 Replies View Related

Queries :: Adding Calculations To Queries Based On Columns In Query

Feb 18, 2014

I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.

Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))

In the VBA, InputDate is defined as a date

Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If

[Code] .....

But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,

Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))

it works.

View 3 Replies View Related

Queries :: Access 2007 / 2010 - Combining Two Queries Into One?

Jan 24, 2014

I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.

The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.

I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.

I am running ACCESS 2010 Tables are ACCESS 2007.

OS is Windows 7 Ultimate.

I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.

View 7 Replies View Related

Queries :: Calculated Field - Top 50 Queries Setting Not Working

Sep 24, 2013

I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!

Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.

Code:

SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT
FROM HeciFail1
ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;

View 1 Replies View Related

Queries :: How To Export Crosstab Queries By Date Parameters

Feb 23, 2015

How can you export cross tab queries by using date parameters (for example: Jan 1, 2014 to December 31, 2014)...

View 3 Replies View Related

Queries :: Crosstab Queries Columns Heading Limitation

Apr 30, 2015

I was wondering how to do a crosstab query and have to column headings

I need the Organization Number and the Org name..so something like this

4005 4010

Office of HQ Office of Accounting

Is this possible?

View 2 Replies View Related

Queries :: Combining Data Results From Multiple Queries

Sep 30, 2014

I am working on a report that has some special characteristics.

Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.

Each of these tables has a date that the Complaint, Complement, and Termination notice was received.

Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.

The equation used for that is : (complaints/vendors_in_group)*100

It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.

What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.

Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.

2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.

3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.

View 4 Replies View Related

Queries :: Totals In Queries - Count Similar Items

Jan 14, 2014

Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.

The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.

View 7 Replies View Related

Queries :: Combine Two Queries Result Of Second Query In Another Column

Mar 15, 2014

i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.

First Query

SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;

Second Query

SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));

View 2 Replies View Related

Queries :: Creating A List Based On Multiple Queries

Jun 23, 2015

I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error

ODBC-- call failed ODBC Driver SQLBase.....

Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.

Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.

View 2 Replies View Related

Queries :: Access Multiple Queries Run With Date Parameter

Jun 19, 2015

I have 5 queries that I am running. The first query has the date range parameters set in the field area that I need to run and each additional create table query is based off the results of the previous query.

1. Which is better to use to run all of the queries in one simple step? A macro or a form? I am exporting the final table to excel so that I can make some additional adjustments off of it.

2. How would I setup the date range parameters for the first query if I were to use a macro without going into the query itself and updating the date field? I tried setting up a macro to run the queries by using the OpenQuery action for each of the 5 queries, but I cannot figure out how to do the date range.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved