Creating Summary Of Data

Dec 19, 2006

I have the detail data in a query working fine in MSAccess 2000 sp3 and I'm trying to create a summary of the data. My query is as follows:

SELECT CUH.CULevel, CUH.AREA, TYPES.Asset, Count(TYPES.Asset) AS Counts
FROM (TYPES LEFT JOIN Broker ON TYPES.[Owner/LastLogin] = Broker.LOGIN_ID) LEFT JOIN CUH ON Broker.COSTUNIT = CUH.COST_UNIT
WHERE (((TYPES.Asset) In ("T30", "DESKTOP")))
GROUP BY CUH.CULevel, CUH.AREA, TYPES.Asset;

with the output like:

CULevelAREA AssetCounts
11000Disease AreasDESKTOP121
11000Disease AreasT3036

but I'm trying to get the output like:
CULevelAREAT30Counts Desktop Count
11000Area136 121

I'm pretty new in MSAccess and I've tried a number of things, but this is the closest I've gotten. Any help from anyone would be greatly appreciated!

View Replies


ADVERTISEMENT

Help Creating A Summary Report

Aug 23, 2004

Lets say I have table with the following information:

OrderNo. ProductId Count1 Count2
01 AA 2 0
01 AB 0 1
01 AC 1 0
01 AD 0 3
02 AD 0 2
02 AE 4 0
02 AB 0 1
.
.
.

A count in Count1 means 0 in Count2 and vice-versa

How do I create a report that looks like this:

OrdeeNo. SumCount1/SumCount2
01 (3) / (4)
02 (4) / (1)
.
.
---------------------------
Totals (7) / (5)


Thank you

View 1 Replies View Related

Forms :: Report Form With Summary Data

Mar 12, 2013

I am trying to create a report or form (not sure which is most suitable) which when opened will show a summary of my data.

My data is essentially a table of timecards, with various linked tables which together enable time working on a lot of different projects to be recorded and reported on, and the value of the work can also be reported on.

In other words it's a bog standard time-recording system.

I can generate a number of reports, for example a report showing all projects with unbilled time and the value of that time.

What I want to do is produce a Key Performance Indicator (KPI) report/page showing total time worked in the last 7 days, month, quarter, year and the annualised amount of the first three of those.

Also I want to show the value of each of those in billing terms, e.g. what was the time recorded in the last quarter worth, and if that continued all year what would be the annual value.

I have created summary (total) queries which return all of these numbers. Essentially each query has a number for the period and a number for the same figure annualised.

I am trying to get all of these figures onto one page.

If I create a report based on one of my queries (by opening the query and clicking Create/Report) Access generates a lovely report showing my two figures generated by that query.

My problem is that I cannot get all of the different figures based on the different queries onto one report (or form).

Access will only allow me to show the figures that come out of the data source for the report or form. I cannot find a way to have multiple data sources.

I have tried creating text boxes where the data source is a different query. I dial up the query and the value, but all I get in the box is "#Name?".

I guess people must make this kind of KPI report or form all of the time. I read about dashboards and the like and see pictures of nice-looking Access pages containing all sorts of summary data, but nowhere can I find any description as to how to create such a page.

View 1 Replies View Related

General :: Summary Report - Real Time Data

Jun 3, 2014

Is it possible to pull the data real-time? I have this access database, and I need to pull the data every time it was updated.

Process name is given, I need to pull the time according to the process name and the volume,

Attached files is the output. The output should be in a form.

View 1 Replies View Related

Forms :: Textbox Control Source - Pull Data To Display On Summary Tab

Oct 16, 2014

I have a form, with a tab control on it. Each tab has a subform to display data. One tab is meant to be a summary tab of the rest, so I want to pull data from certain controls on each of the other tabs to display on the Summary tab.

If I set the ControlSource to Forms!subfrmLABOUR!txtTotalHours.Value the control just displays #Name?

I assume that means it can't reference the ControlSource. Is it just a syntax thing? I've tried various methods, but no luck.

View 6 Replies View Related

Creating A Query / Report That Displays Data From Two Tables And Total One Set Of Data

Aug 10, 2012

I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.

What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].

So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].

Here is a link to an Example Database [URL] ....

I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.

And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.

View 14 Replies View Related

Counting Summary

May 16, 2005

hi
i have a number of query's (Current memberships, out of date memberships etc) all via a certain area/town.

i am trying to find out total figures (how many members how many non members, how many in certain area/town. these need to be updated continuoulsy.

do not no how to approach i ahve looked at the sigma sign and played with no luck.

should i be looking at another query for totaling or un update qurey, sorry very lost, would like this information also displayed on my record form.

searched all weekend with no luck any ideas.

View 2 Replies View Related

Summary Of 5 Marks - MIN And MAX

Feb 25, 2012

I try to make my second database in Access. I need to summary 5 numbers, and then minus the lowest and the highest of them. For example (1+2+3+4+5)-1-5=9

In excel it looks like this: =SUM(C2:G2)-MAX(C2:G2)-MIN(C2:G2)

In access (Expression builder) i made this code: [J1]+[J2]+[J3]+[J4]+[J5] - it's the summary of five marks, but how can i erase the highest and the lowest one?

View 3 Replies View Related

Time Summary Question

Feb 14, 2006

I am looking for the best way to produce a form and/or report that will count how many appointments are scheduled by every tenth of an hour for every hour. Here is what I have for an expression in a query for 1:07 PM - 1:13 PM:

7-13Start: (IIf([Startminute]="7" And [StartHour]="13",1,0)+IIf([Startminute]="8" And [StartHour]="13",1,0)+IIf([Startminute]="9" And [StartHour]="13",1,0)+IIf([Startminute]="10" And [StartHour]="13",1,0)+IIf([Startminute]="11" And [StartHour]="13",1,0)+IIf([Startminute]="12" And [StartHour]="13",1,0))

This works but will take the 288 expressions to get what I need because the requirement is to show schedule start AND schedule end numbers. I am getting tunnel vision and would like to know if anyone has any better ideas.


Thanks,
Rich

View 1 Replies View Related

SQL For Financial Summary From Transactions

Oct 20, 2005

I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.

I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.

I do not want to use record by record VB because it is slow and I have a million+ records.

Can anybody help ?

Transactions Table (input)

Account Period Amount
1011100
1015 100


Account Summary (desired output)

Account Period Opening Movement Closing
101 10100100
10121000100
10131000100
10141000100
1015100100200

PS I need to run on Access and SQL Server

View 1 Replies View Related

Query Results Summary?

Aug 29, 2006

I'm trying to build a database that will calculate the price of meals. I have a table 'ingredients' that has the ingredients, measurements and prices. As prices can change regularly I assumed that inividual recipes should be queries that calculate the total cost on the fly. My problem is that I regularly need to print out a summary of costings that includes total costs for each recipe.
From reading various posts I have formed the opinion that I REALLY shouldn't store calculated results in a table.
Is there another way to get my summary? Or will I have to resort to being an access pariah?:(

View 2 Replies View Related

SQL Query To Create Summary

Dec 11, 2006

Hi Guys!

It's me again.

From one of my tables, custinfo, i want to generate a summary of records. What I mean is I want to count the total number of records, count those who doesn't have address entries, count those who doesn't have credit records. So in presentation, I want to have this:

Field No Records With Records Total
Address 10 256 266
CreditREc 5 261 266

Is it possible?

Thanks!

View 3 Replies View Related

Weekly Summary Of Tasks

Sep 17, 2007

I am just starting to use Access and have set up many simple forms, datasheets, reports, summaries, etc. I have not worked with macros, etc. and need help for this project. I have set up a task table

F1 - DateOfCall - date - now()
F2 - Client - text
F3 - DateCompleted - date
F4 - DueDate - date
F5 - problemDescription - memo
F6 - Resolution - memo

I need to run a query and/or report every week summarizing what has been done the past week. Would also like to view previous weeks. I need help. How do I go about doing this.

View 1 Replies View Related

Checkboxes Used To Show Summary Info

Sep 12, 2005

I have a form where I display the client's information and a series of checkboxes showing which services have been registered to them (ex. telephone, internet, cell phone (all info is held in separate tables with client id as the foreign key...).

For instance: I have a working query that pulls the client's id from the form to search the Cell Phone Table to see whether the client has a cell phone registered to them or not. How can I get that to translate into the checkbox on a form?

It seems so simple, yet the solution isn't presenting itself.

Any help is appreciated.

View 14 Replies View Related

Summary From Subforms On Main Form

Feb 17, 2006

Hi everyone

I use Access 2003 and have created a form with several subforms which I've placed onto a number of tabs. I'd like to have some of the most recently entered data from the subforms displayed on the main form.

For instance - if one of the subforms details the repairs to my car, in terms of date, items repaired, name of the mechanic and the cost, I'd like the most recent date of repair to be displayed on the main form. I know that I can see it be clicking the "Car Repairs" tab then scrolling down the information, but I'd like to have it displayed for easy viewing. I'm not sure if I'd need to have some VBA to do this or if it can be achieved by, for instance, creating a query to populate the appropriate textbox on the main form.

Thanks for your time and patience!

Juan

View 6 Replies View Related

Forms :: Displaying A Summary Of Many Fields

Jul 3, 2015

I have a situation where I use the output of a combobox in a query. When I run it everything is fine.Now I want to make a summary of 9 of these text fields in the query. However, when I do this only the bound columns show up in the summary field. I can't get a summary of the second columns of the combo in the query.

I have been using the following successfully with regular text fields:

Summary: [Comodity] & "," & [Comodity1] & "," & [Comodity2] & "," & [Comodity3] & "," & [Comodity4] & "," & [Comodity5] & "," & [Comodity6] & "," & [Comodity7] & "," & [Comodity8] & "," & [Comodity9]

View 9 Replies View Related

Multiple Summary Total In A Report

May 19, 2015

How can I generate a report that contains multiple summary total by year. I run the query to give me selected time period(s) for my report. For example: I want to have the summary for the date ranges from the year 1994 to 2001, and then 2002, 2003, 2004 etc. I'm having difficulty for the first summary total which is the date range from 1994 to 2001.

View 3 Replies View Related

Query To Get Summary Results For Multiple Queries

Feb 27, 2006

Hi All,

I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.

The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:

QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -


So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.

Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.

The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.

Thanks,

Evan

View 2 Replies View Related

How To Obtain A Table Summary Using QUERY Function?

Jun 21, 2007

Hi..(",o) anyone can HELP?

I'm currently using MS Access 2000...
May i know how can I translate the following TABLE

Doc# Team# Status
D1 TA Done
D1 TB WIP
D1 TC WIP
D1 TD Done
D2 TA Done
D2 TB Done
D2 TC Done
D2 TD WIP

to a QUERY reflecting in the following format?

TA TB TC TD
D1 Done WIP WIP Done
D2 Done Done Done WIP


Your help will be greatly appreciated!

View 2 Replies View Related

Reports :: Summary Of Populated Fields In Each Record

Sep 6, 2014

I have a very simple single-table database with 23 fields. Some of the records have only two or three fields populated. I would like to be able to print a summary of only the populated fields in each record.

It would Ideally look something like:
Record 1 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content - Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content
Field 21 Title: Field 21 content - Field 22 Title: Field 22 Content

Record 2 Name
Field 1 Title: Field 1 content - Field 2 Title: Field 2 Content - Field 5 Title: Field 5 content
Field 10 Title: Field 10 Content

Record 3 Name
Field 11 Title: Field 11 content - Field 12 Title: Field 12 Content - Field 21 Title: Field 21 content
Field 22 Title: Field 22 Content

View 9 Replies View Related

Queries :: Create Summary Query For Each Table?

Dec 5, 2013

I have three tables with data.

Table1 is data for meals.
Table2 is data for room costs.
Table3 is data for payments made.

Each of these tables has a foreign key for EventID.I'm trying to produce a report that will show, for each EventID:

The total billed (which is meals + rooms)
The total paid (from Table3)
The balance due (the difference from the two above).

Do I have to create summary queries for each table?

View 2 Replies View Related

How To Create A Summary Page Based On Selection

Sep 21, 2015

I created a database with a list of companies, with many details in separate tables linking them - shareholders, directors, address etc.

Now i want to create something where the user will be able to just Select the company, and the information would be shown below just for that company. I would want to design of course in order to appear nice.

View 10 Replies View Related

Creating A New Data Base From An Old One.

Nov 8, 2005

I want to create a new data base from an existing one in Microsoft Access. I want to use all the fields but not the data. Can I do this? I also want to keep the form that I created. Can anyone give me suggestions? Thank You!

View 1 Replies View Related

Reports :: How To Combine Queries Into A Summary Query Of Sorting

Mar 21, 2013

i intially, I wish to have attendance stats for 3 groups of people over 2 days in a report

i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?

View 1 Replies View Related

Queries :: Summary Of Columns In TSQL To Return -1 If Not Equal To 0

Sep 3, 2014

Is there an efficient way to return a -1/0 instead of some number/0?

A table has 4 outer joins to views that return a value or null depending on if that field represents a Federal lease.

These 4 outer joins work.

The result is a ID_Wells column and 4 columns with any combination of values or Null.

This line of TSQL:

ISNULL(ISNULL(dbo.vRE_FedLeaseType.Fed_Lease_Type, '')
+ ISNULL(dbo.vRE_FedLeaseMinOwn.MinOwn, '') + ISNULL(dbo.vRE_FedLeaseSurfOwn.SurfOwn, '') + ISNULL(dbo.vRE_FedDirHzLease.Expr1, ''), '') AS HasFedLease

This creates another final summary column [HasFedLease] that returns 0 or some number.

For the Rule Engine - 0 is False, any other number is True.

Access links to this SQL View. Is there a quick way with out a UDF to convert numbers > 0 to a -1?

Code:
SELECT dbo.Wells.ID_Wells, dbo.vRE_FedLeaseType.Fed_Lease_Type, dbo.vRE_FedLeaseMinOwn.MinOwn, dbo.vRE_FedLeaseSurfOwn.SurfOwn,
dbo.vRE_FedDirHzLease.Expr1 AS CountOffedLeaseDirHz, ISNULL(ISNULL(dbo.vRE_FedLeaseType.Fed_Lease_Type, '')

[Code] ......

View 3 Replies View Related

Summary (Count) Of Multiple Date Fields By Month

Feb 13, 2014

What I have: A table with multiple Date/Time columns. Here's an example:

What I would like: A month-by-month summary counting the number of entries in each of the three columns, like this:

I'm indifferent as to what happens with the months zeros across all three fields (whether they show up in the report with zeros or are omitted altogether).

Smaller Pieces: I am able to create such a summary for one column at a time:

Not what I'm looking for: I can not figure out how to create a query that does this for all three columns. My best attempt was a disaster:

I have attached the example file for your convenience: Database1.accdb

To clarify, my issue isn't the format of the date. I know about the Month(...) function. My question is regarding how to count this data and split it into a month-by-month summary (see the picture above in the "What I would like:" section)

View 6 Replies View Related







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