Query To Give Daily Balance Across Bank Accounts? (Then Plot In A Graph)

Feb 22, 2015

How to build query to give daily balance across bank accounts? (to then plot in a graph)

Assumptions:

* There is a table TRANSACTIONS which includes columns TRANS_DATE, AMOUNT and BANK_ID. It does NOT include a column for balance. So current balance for a bank account is the sum of the AMOUNTs for that BANK_ID for example. Balance on date XX will be the sum of all AMOUNTS for that BANK_ID for all TRANS_DATE's prior and including the date XX.
* Table BANKS which has BANK_ID and TITLE

Would like a query that gives: Supply StartDate and EndDate for the query:

Date Bank1Balance Bank2Balance Bank3Balance TotalBalance
1/1/15 $100 $200 $100 $400
1/2/15 $200 $200 $100 $500
.
.
.

etc

View Replies


ADVERTISEMENT

Tables :: Suitable Table Structure To Distribute Payments Among Different Bank Accounts

Aug 17, 2015

Looking for efficient table structure for this. Let's say I have a list of contractors who will get paid a certain amount of money each week. The amount of money changes each week by measuring the amount of work done, giving it a price and calculating it. The fields are these:

[pk]WeeklyWorkID
WorkID
Quantity
UnitPrice
Total=Qty*UnitPrice (not stored in this table)
EmployeeID
WeekID

So far, it's all good, but we wish to pay the contractors in an unusual way. Let's say he makes $16,000 of work this week, and we want to pay him using cash AND/OR either one or multiple debit cards. For example:

Cash: $1,500
Debit card 45: $7,500
Debit card 71: $4,000
Debit card 13: $3,000
Total = $ 16,000

We can only deposit $7,500 max in each card. But it's even worse... two or more contractors can SHARE the card and it's not always the same card. Sounds pretty funky, but it's what the company wants to do. So, what would you recommend me in this case?

I'm guessing I'm gonna need these tables: tbWeeks to hold the week number, tbCards to store the cards' info, and an extra table to make a many to many relationship between the cards and the employees.

View 2 Replies View Related

Reports :: Change Plot Order For Graph In Access 2010?

Nov 28, 2014

I have a column graph I created for a report.

The Y axis has number of days.

The X axis has Member ID.

When I make the column graph the data seems to not be in any order. The report is in order by the number of days in ascending order.

How can I have the columns with the largest number show up first and the columns get smaller. I would like the largest column all the way to the left, proceeding the next one in size so it goes from largest to smallest.

View 5 Replies View Related

Queries :: Split Quantity And Give Running / Accumulating Balance

Oct 21, 2014

I like to have my query splitting quantities and give me running/accumulating total.

See attachment as what I'm currently getting and what I would like to get.

View 5 Replies View Related

Modules & VBA :: Email Query - Changing FROM ID If Have Multiple Accounts?

Aug 5, 2013

How do I change the account from which to send the email if I have multiple accounts, or can I?

View 14 Replies View Related

Losing The Plot With Subform Update!

Nov 16, 2005

right I have a subform which holds assessment data
it is a tab-control form which monthly assessment data each tab has its own form i.e. "frmCurrentAssessment-October2005", "frmCurrentAssessment-November2005" and they are based on their own queries i.e. "qryCurrentAssessment-November2005". It will not let me update the subform which is joined to frmAllStudents which is based on "qryallStudent"

the form and the subforms are joined by Admission Number. I can update directly from "qryCurrentAssessment-November2005". But the form wont let me update, I have already checked that the fields arent locked or that the form isnt uneditable and everything seems fine.

I am at a complete loss as to what to do here, is there something obvious that I havent checked

View 1 Replies View Related

Running Balance In A Query

Mar 13, 2006

I have a query that have three columns invoice number, tran number and amount. What I would like to do is to sum the invoice number if they are equal. example:

new column
Invoice #: 1345652 50.00 50.00
Invoice #: 1345652 65.00 115.00

I have the same invoice multiple times, but the transaction number is different. so I need to SUM all the invoices that are the same.


Please help!!!!!!!

View 5 Replies View Related

Calculate Balance Query

Aug 22, 2007

Hi All.
I have Request, Device and Receive table. All table has DeviceID. I create query to calculate total amount of each type of devices which was requested by link Request and Device tables. And query to calculate total amount of each type of devices which was received by link Receive and Device tables. I would like to create Balance query to calculate:
Total_Receive - Total_Request = Balance
If devices in Request and Receive table is the same result is correct. But if in Receive table add new type of devices I cannot see amount of new device in Balance query. How to fix that problem?
Thanks.

View 1 Replies View Related

Copy A Query And Give New Name

Mar 11, 2015

I have Access 2010 and I have a query that I want to take a copy of, save as and call it call it something else in the same database.

For example "query1" I want to save as and call it "query2" .

View 7 Replies View Related

Making Query Give Me Anything For Dates BETWEEN 10/01 And 10/15

Nov 10, 2006

Hi!

I'm trying to write a query that will display my data for anything that has a date between 10/01 and 10/15. I went into the "Build" area and looked at the "functions" available but none of them sound right - and the one book I have on Access is not so great.

Can someone help me with the correct formula?

Thanks!

View 2 Replies View Related

Query To Pull Most Recent Account Balance

Nov 6, 2007

I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:

Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT

What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;

What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;

Am I trying to do something too complicated for the query language?

View 5 Replies View Related

Can't Query For Average Balance Sytax Errors

Feb 17, 2008

Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D

View 2 Replies View Related

Query To Pull Most Recent Account Balance

Nov 6, 2007

I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:

Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT

What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;

What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;

Am I trying to do something too complicated for the query language?

View 6 Replies View Related

Counting With A Query To Give Results On Report

Dec 18, 2007

I have a query that pulls up information on employees when they receive warning notices. I would like the query to give me some type of warning (report, email, etc.) when an employee has three or more notices.

Can someone help me with this?

- Thanks in advance!

View 10 Replies View Related

Query On Form. (combo Box To Give Criterea)

Mar 22, 2006

I have changed my approach for my database, and i think a query is what i needed all along. But i am not sure how to go about it.

Is it possible to have query results on a form?

more precise, i have a table full products, alot of information per product (about 30-50 fields)

The amount of actual products isn't many probably around 10, and it's likely to stay at around 10 products.

Can i have a drop down list listing all the products, so it looks these up from the product table, you select the product, that product field value becomes the criterea for the query, and all the associated form fields get filled out to do with the product selected.

To summarise, can i have this all done in a form? so it's easy for staff to simple go the to product drop down list, select a product, all the info relating to the product is then displayed on the form.

hopefully i have made sense with my post.

View 10 Replies View Related

Daily Total Query

Sep 7, 2006

Hi,

I have a database, in which I need to add up the total number of entries made at any point of the day. I have started by creating a query with the entry's SerialID (unique) and the date it was entered, however I am stuck already.

Any help would be appreciated,

Ben

View 12 Replies View Related

Query Date For A Daily Report

Sep 24, 2004

I need to generate a report with records generated on a daily basis and I am looking how to write the query expression. For example, my Access database generates record on a daily basis and I an using a query to generate the report. I only want to report the records created on that day, and then each subsequent day. So, in the query, I am looking for the correct expression to grab only those records generated that day. (Hope its not confusing, I'm new at this)

Thanks for any help in advance!

Keith

View 6 Replies View Related

Queries :: Balance Update Query - Data Type Mismatch In Criteria Expression

Jul 25, 2013

I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.

Here's what it looks like in design view:

Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]

Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));

I get the error: data type mismatch in criteria expression when I run it.

View 3 Replies View Related

How To Create MDB-bank Without Access ?

Aug 25, 2006

Is it possible to create MDB-bank without Access ?

Thank you very much!

Aleksandra

:)

View 4 Replies View Related

General :: Query For Daily As Well As Cumulative Production For Month

Jan 28, 2013

I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as

date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |

I want this on a single query so that i can make a report out of this .

View 12 Replies View Related

Queries :: Query With Daily Fields Based On Parameters?

Feb 11, 2014

I am trying to create a query that will provide a field for each day of a month. However, I want the query to be able to work for any month that I want to run on based on a parameter. Basically I want this:

Day 1: Sum(IIF([ReleaseDate]=#[# of Month]/1/[# of Year]#,[GamesSold],0)
Day 2: Sum(IIF([ReleaseDate]=#[# of Month]/2/[# of Year]#,[GamesSold],0)
and so on for 31 fields.

This is not currently working.

View 8 Replies View Related

Queries :: Calculating Daily Change In Access Query

Jan 21, 2015

I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.

A. Date B. Price C.Ydayprice
01/01/2015 101.45
02/01/2015 104.70
03/01/2015 103.00

Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.

View 3 Replies View Related

Saving Daily Generated Query For Later Lookup By Date?

Feb 26, 2014

I have a query that reports the number of students that are currently enrolled in a given number of schools within an area for that given day that the query is ran. This number can fluctuate greatly day to day, so is there a way to create a table which can hold this information so that I can go back in history and look up what our numbers were on whatever given day (date). (The example below is just a representation of what I actually have.) The School number would be unique, as would the date (I assume), I would like to see all of our school on whatever given date to see what our numbers are or were.

I have an example of the query currently I have set up in access 2007:

School # School Name Qty Date
001 BLUE 300 02/26/2014
002 RED 468 02/26/2014
003 YELLOW 10 02/26/2014

VS

School # School Name Qty Date
001 BLUE 310 02/27/2014
002 RED 400 02/27/2014
003 YELLOW 8 02/27/2014

View 3 Replies View Related

Graph (2 Fields From Query On X And Y-axis)

Jul 26, 2005

Hi!

I have this table from a query:

1 1,34
2 1,23
3 1,21
1 0,65
2 0,55
3 ...
1
2
3
1

And I want 1,2,3 at the Y-axis and the X-axis to be the other values (1,34...)


But I only get a graph that shows 1,2,3 at Y but only the value 1 at the X-axis and the 1,34-values as legends! Why?

View 1 Replies View Related

Graph Date Range From Query?

Jun 18, 2012

I have a graph which queries data from a table, I need the graph to show the dates in the table with the correct results specific to that date.

However on the graph its displaying dates not in the table so its plotting the results out wrong, I need it to only show the dates in the the table on the bottom axis.how to do this.

View 1 Replies View Related

Odd File Format For Import By Bank

Jun 19, 2007

Hi:

Working with a bank that wants a file from us so they can import into their check reconciliation program. Got the export from the accounting program working for the info they need, but the bank guy says he needs a file structure like this:

(first row is header..don't worry about what the stuff means for now)
1234578990000000000000000
(rest of rows are details)
1234589000807091234
1234589000807093456

notice the line breaks between header and rows? Wouldn't this call for a line break and mess up an import program? Bank guy isn't a database person and is clueless

Thanks......

View 2 Replies View Related







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