Plz Help! Query For Summing Individual Records On Their Own

Jun 27, 2006

Hello Everyone,

I am trying to perform a sum of records for the table below

SeatType| Ext rent | |Internal rent |
Work Transfer 2 2
Vendor
Other 2 3
LOB BRF
GTI BRF 2 5
Displacement


so that when I run the query I get the following following


SeatType| Ext rent| |Internal rent| | Total |
Work Transfer 2 2 4
Vendor
Other 2 3 5
LOB BRF
GTI BRF 2 5 7
Displaceme


What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?

Thank you.

View Replies


ADVERTISEMENT

General :: Emailing From Access - Individual Records To Individual Emails

Jan 8, 2015

I have a database where I record grades for my students. I would like to create a macro so that every time I record a grade as an "F", the student will receive an email that will contain an attachment of their scores. A lot of the information I want to include in this email is in several different tables. So to make it easier, I have created a query that lists the following:

Students name (SName)
Student's email (SEmail)
ID# of the form I use to record their grades (ID)
ID# of the studen'ts work (WorkID)
Date of their work (CreatedDate)
Date I graded their work (AuditDate)
Grade="F" (Accuracy)

How can I create something that will automate emailing of this information--to the individual student with only their information? I am using Access 2010. If I can't automatically send the emails, I would like to create a button in my audit screen that will send a report containing that student's information to them.

View 1 Replies View Related

Queries :: Summing Records In A Query?

Jul 24, 2013

I have a query which displays sales and cash deposits for the day. I want a sum of all cash deposits.

So it looks like this..

Date________________Amount_______________SumOfAmou nt____________Exp1
17/7/2013___________$55___________________$55__________ __________$55
17/7/2013___________$22___________________$22__________ __________$22

I tried doing it by two methods.. neither of which are working.

In the Amount field, I put it as a Total: Sum. I also tried creating a new field which is an expression which sums up the Amount field. Neither are summing it and just displays the value.

View 1 Replies View Related

Modules & VBA :: Send Individual Pdf Attachment To Individual Recipients

Feb 3, 2014

I have created a database that generates a report with customer debts. I have a lot of customers and in a report each page is a different customer with individual debts info. What i would like to do is to create a button and distribute individual report page as pdf to individual customer by email. I'm using Lotus notes. I have searched the forums i found something but it was not what i was looking for.

View 1 Replies View Related

Having Individual Records, In Multiple Groups

Jul 14, 2006

i am making a contacts database. I have a table to hold the names and addresses of 700 people. I have another table holding the names of the mailing groups those 700 people can be in. One person can be in multiple mailing groups.

i made another table, and i typed in all the people and the multipe groups they were in. For example, a there would be seven records for a person if they belonged to seven groups

then i made a query relating that table to the addresses table. I want to be able to open forms to show an individual group, and cycle through the people in that group only.

i can do this, but access wont let me edit any of the records.

is there somthing wrong with my relationships? how do i tell accesss to allow records be in multiple groups?

View 1 Replies View Related

Reports :: Button Associated With Individual Records

Aug 21, 2013

I have a button on a report that I want to use to confirm if the contents of a field were prepared or not. Below is the button vba:

Private Sub Command60_Click()
'retrieve the records
Static Count As Integer
Count = Count + 1
'
Select Case Count

[Code] ....

The problem with this is that it changes the text and background for every button in the report. I want the button to change its text etc for individual records in the report. and not all at one time. In other words I want each record to have a button, and each button associated with each record.

View 1 Replies View Related

Forms :: Grouping Individual Records As A Set?

Apr 16, 2013

I wish to make an Access DB that will work as a HUB for a few different people in our company to work off of as a means of putting analytical results in tables where a SampleID is given to each sample and is unique. I want to know if there is a way to group any given number of SampleID records together into a 'ProjectGrp' and produce something along the lines of a work order form so the other people in the company can look at a ProjectGrp, see what SampleIDs are part of that group, and then fill in the data for each SampleID.

View 3 Replies View Related

Lock Individual Records After Saving

May 4, 2014

what i want to do is lock each individual record created by each user from the other 3 users after they have been saved ( I am not referring to record locking/table locking while updating/creating ). ie They can all read each others created records BUT only update/modify their own created records. They all have individual passwords to log in.

View 3 Replies View Related

Summing Records Where Field Is Between

Jul 31, 2006

I am trying to create a sum of a field called ec2mo where mopp is between 0 and 2 and then another sum when mopp is between 2 and 4, etc... I've tried a number of different things, but it doesn't seem to work - instead i get a single value for each field (i'm assuming because there are different values for mopp and many other fields)... I've attached the db with just that table.
Thanks very much for your help!
P.S. I'm not really at all good with using SQL so anything that can be done in a regular query would be most helpful!

View 3 Replies View Related

Generating Reports For Individual Grouped Records

Oct 19, 2006

This probably is a very stupid question but I am a new user and have been pulling my hair out for a week over this. I have created a report and linked it to my table but it generates a list of all of the values in my table on the report. What I want is a way to generate a report page for every row in my database table grouped on a key record that is unique to each data row. If anyone has any advice it will be much appreciated, thanks.

View 1 Replies View Related

Forms :: Running Code On Individual Records

Mar 20, 2015

So in my database i'm creating a live form that will open up a list of patients and when they are seen. one of the bits is a button that before they arrive says "Not Arrived" and after they arrive it says "Arrived". The code works fine:

Code:
Private Sub Command68_Click()
Dim C1 As String
Dim C2 As String

[code]...

The problem is after clicking the button on any of the records, it changes the text on all records, to wit: I tried to attach images or links to images, but my post-count is too low. The before-click screenshot is /3QnBkgG on imgur. The after-click screenshot is 9VZWzzp on the same site.

Bear in mind I've only clicked the button (it's white, so it doesn't look like a button) on the top record.The form is set to continuous forms, not sure if that has anything to do with it.how I can get the code to run on individual records, rather than applying the first-record's results across all records?

View 12 Replies View Related

General :: Lock Individual Records After Saving

May 4, 2014

I have 4 users all with the same rights. What I want to do is lock each individual record created by each user from the other 3 users after they have been saved ( I am not referring to record locking/table locking while updating/creating ). i.e., They can all read each others created records BUT only update/modify their own created records. They all have individual passwords to log in.

View 4 Replies View Related

Modules & VBA :: Summing Multiple Queries And Summing Time

Oct 4, 2013

I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;

1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings

These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).

I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).

View 1 Replies View Related

Summing Records Based On Criteria...

Mar 25, 2006

I am not sure if this possible, the methods I have looked up on the Internet take ages for the query to open but I'll give it a shot.

What I need to do is count (and increment) how many records are returned in a query for each person.

So if 1 person returns 4 unique records, in these records it shows 1,2,3,4 in each record.

The way i have tried is by using DLookup to check the next record and evaluate it using IIF but this takes about 25 seconds to return circa 50 records.

View 5 Replies View Related

Summing/Counting Records In Access

Dec 29, 2004

Hello & thank you in advance.
I am fairly new to using Access and would appreciate any help.

I have a file with 50,000 records
Example:
NAME CATEGORY
Karen Shoes
Karen Sports
Karen Collectibles
Karen Cars
Bob Music
Bob Sports
Bob Cars


The results that I am looking for are a sum of the total category purchases by each name.
EXAMPLE:
NAME CATEGORY
Karen 4
Bob 3

Is that something that I can do via Acces?

Thank you

View 4 Replies View Related

Individual Records Exported To Word Mail Merge?

May 12, 2005

Hi I wonder if anyone can help? I have looked through prevous posts and cant find any answers that fit what I need to do. For reference I am using Access 97 and Word 97.

I want to be able to have a client record open in a form and click a button and for that particular record to merge with a preformatted word doc. I have created a query that gives me all the fields I need (as they are over multiple tables) and I can successfully combine this into a mail merge. But it currently takes *all* the records and I want it to only do the current one. I also want user to be able to click a button somewhere on the form that automates this process for him. Is this possible?

I have investigated transfertext and filled in what I think I should have but it seems to do nothing. It creates another (unopenable) file in the same folder as the word doc and does nothing else. But I am using the 'export word for windows merge' in the tranfertext action?

If I can only crack this I will be happy! Many thanks in advance for any help

DD

View 3 Replies View Related

Trying To Have Sums And Individual Records On The Same Page (picture Included)

Sep 8, 2006

well i tried asking for help on this earlier, but i wasn't able to find any help so here is my problem.http://img330.imageshack.us/img330/2850/pictureaccessro7.jpg (http://imageshack.us)the sales - net = profit is fine but what i want to do is have a totals at the very bottem. everytime i try to do this, i can only get the totals to show and each individual record dissapears, or nothing shows at all. i've tried using a report but i can't seem to pull it off either. if anyone knows what i'm talking about and knows what i have to do then please help me :)

View 4 Replies View Related

General :: Delimiting - Separate File Into Individual Records

May 24, 2013

I am trying to import several hundred records into Access from a txt file but I can't find a way to separate the file into individual records. A portion of the file is given below:

'C Olive Teece', 'Census, 1940', 'birth: 1919 New York', 'residence: 1940 Ward 4, Canandaigua, Canandaigua City, Ontario, New York', 'spouse: John H Teece', 'parents:', 'children:'
'Edith Teece', 'Census, 1940', 'birth: 1888 England', 'residence: 1940 Smithtown Town, Suffolk, New York', 'spouse:', 'parents:', 'children:'
'St Clair Teece', 'Census, 1940', 'birth: 1867 Pennsylvania', 'residence: 1940 Ward 3, New Castle, New Castle City, Lawrence, Pennsylvania', 'spouse:', 'parents:', 'child: Edward C Teece'

View 14 Replies View Related

Grouping And Summing Mltpl Records To One Record

Jun 19, 2006

:confused: Hi everyone,

I have one table that I want to query. It's a basic table that contains:

MYKEY, AMOUNT
1234430, 100
1234435, 200
1234435, 100

1264435, 300
1264430, 200

1154435, 100
1154435, 219


1294430, 983
2010, 845
........ so on and so forth


Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:

MYKEY, AMOUNT
1234430and1234435, 100+200+100 --> (123 being the first common group)

1264430and1264435, 300+200

1154435and1154435, 100+219

etc etc.

i tried using simple sum and group by but does NOT work:

SELECT MYKEY, SUM(AMOUNT)
FROM MYTABLE
WHERE MYKEY LIKE ('*4430') OR ('*4435')
GROUP BY MYKEY

It does NOT ROLL-UP the 4430 and 4435 together.

QS: do i need to use VBA for this?

QS: do i need to have subqueries?

QS: please help?

Thanks!

View 4 Replies View Related

Summing Number Of Records In A Group On Report

Mar 28, 2013

I have a report I am grouping by a category, and I want to put the total number of records in that category in a textbox in the group footer.

How would I go about doing this? I have searched high and low with no avail...

View 2 Replies View Related

Forms :: Popup Subform - Password Protect Individual Records

Jul 23, 2014

So I created a new form that multiple coworkers will have access to. I understand that you can password protect an entire data base, but I was wondering if you could go deeper than that. I have a main form with a combo box (query to show existing IDs) for "ClientID" that links the main form and subform (Its controlsource is ClientID from the table the subform was created off of). If a user selects an existing ClientID, the linked subform pops up with the selected ClientID displayed on the subform in a field along with existing additional information displayed. If The user types in an ID not in the database, a blank subform comes up for the coworker to create this new record and the field for ClientID is blank.

1) How can I make it so that when a coworker types in a ClientID in the combobox that is not in the database, the blank subform that pops up has the ClientID field filled out with what was typed in the main form.

2) Situation: Adding a new client to the main form, and filling out the additional in on the popup subform. I want there to be a field on the main form for "worker". The worker will put their ID and create a new client record. Is it possible to make it only possible for this worker to edit this client record in the future? ...the original creator will be the only one able to edit the information.

-I am thinking maybe have a table with all workers and associated passwords. So, if a user tries to open a subform created by another worker, he will be prompted to enter a password (which will be the one associated to the creator in this "secret" table.

View 5 Replies View Related

Summing Query.

Aug 18, 2005

hello there...ive a question...i have a summing query which searches for the word "processed" in a specific column of a table...currently if "processed" is not found within the column the query will not return anything...is there a way to have the query return a zero instead?

View 3 Replies View Related

Query On Summing

Feb 15, 2007

When i'm summing up values in a column like "Hours:[production]+[nonproduction]" how can i have access treat blanks as zeros? I tried
"Hours: IIf([nonproduction]="",0,[nonproduction])+IIf([production]="",0,[production])" and it just gave me a #value error

View 2 Replies View Related

Summing #s In A Query

Sep 8, 2004

Hello,

I'm trying to sum numbers in a query. The table that Im summing from is set up as follows. The 1st column is just an autonumber. The 2nd column is an individuals. The third column is a date. The fourth column is a transaction (Buy or Sell). The fifth column is an amount (in $). and the 6th column is an Account (RRSP or RESP).

I would like to sum all the buys for each individual for each account. However I'm finding this difficult to do. Here is what i have so far. It sums all the 'buys' for the entire table.

SELECT Transactions.[Customer ID], DSum("Amount","Transactions","Transactions.Transaction=1") AS Expr1, Accounts.[Account Type ID]
FROM Transactions INNER JOIN Accounts ON Transactions.[Account ID] = Accounts.[Account ID];

Thanks very much,

C

View 7 Replies View Related

Summing Totals In A Query

Sep 22, 2005

SELECT Commissions.TransactionID, Commissions.OriginatorID, Commissions.AmountPaid, Commissions.DatePaid
FROM Commissions
WHERE ((Commissions.DatePaid)> DateAdd("d", -32, Date()));

I want to add up commissions paid in a month using a simple query and im not sure how to proceed from here. This example will pull records for the last 32 days, but now how do I do the sum commissions.amountpaid to work?

Thanks for any help.

Scott

View 5 Replies View Related

Summing Two Fields In A Query

Jul 17, 2007

I have 2 fields, Accounts, Accounts CMO. I need those two fields to show in the same table in a new field called Total Accounts. I am summing them, but getting irregular results, some are right, and some are 4 times more than they should be? How would you go about doing this, maybe I am missing something.

View 1 Replies View Related







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