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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 25, 2006
Is it possible to create MDB-bank without Access ?
Thank you very much!
Aleksandra
:)
View 4 Replies
View Related
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
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
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
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
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
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
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