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:
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:
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.
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.
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
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:
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.
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.
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:
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?
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
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:
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?
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.
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.
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.
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)
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.
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 .
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.
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.
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
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.
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
