my details input as per below;
1) table 'Receiving' (Part#,Qty received,date received)
2) table 'Issuance' (Part#,Qty issued, date issued)
How to calculate current stock on-hand?
*previously this report are using ms excel table. i just tried to convert into access since i will have a better view, will a separate form to fill up, will have a report to pull in(hopefully... current on hand )
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 currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.
Monthly Payment Period: 1 to 300
Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.
Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12
Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.
The final Balance Remaining should be exactly £0.00.
I'm currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.
Monthly Payment Period: 1 to 300
Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.
Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12
Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.
The final Balance Remaining should be exactly £0.00.
I am trying to create a suitable expression, either on the form itself or in the underlying query, that calculate the balance after each transaction, like a bank statement. What is the best way to do it?
Paul
Access 2010
I have a report with some text boxes on it. Sometimes the text in these boxes can be a very long string of characters (maybe a path to some folder). In this case I do not want the text box to grow. I just want the report to show me the right-hand side of this path and cut off the left. How do I do this when still aligning my text to the left?
If I have the field name as below:
Oil quantity
Accumulate Oil quantity
Date
Recordnumber
I want to get value of "Accumulate oil quantity" of current record to be equal to sum of "Oil quantity" from first record till the current record:
Let say
after I input "oil quantity" in the record # 1 ,2 & 3
The "Accumulate oil quantity" of record value show below:
"Accumulate oil quantity" of record#1 = "Oil quantity" of record#1
"Accumulate oil quantity" of record#2 = "Oil quantity" of record#1 + "Oil quantity" of record#2
"Accumulate oil quantity" of record#3 = "Oil quantity" of record#1 + "Oil quantity" of record#2+"Oil quantity" of record#3
Anyone know , please help
Thank you so much
Is it possible to set an access database to take a depreciation value of lets say 10.00, and subtract this from the current value every month?
So far my database has a form where you can input the depreciable life, purchase price, current value and total depreciation. However the date and amount of depreciation has to be added manually into a data table for the text boxs to calculate how much it has depreciated and the current value.
Is there a way to take today's date and calculate the last day of the last month? Without the user needing to enter any parameters. In other words, if I ran the query today with this criteria, it would only show information for April 30, 2013. Is that possible?
View 3 Replies View RelatedI have a query (Inventory Transactions Extended) in which i am trying to caculate current stock.
I have a form (Inventory Transactions Form) where i either add or remove Inventory Items.
I have used this statement to create a new field (Actual Quantity) to calculate current stock based on stock been added and removed:
Code:
Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition";[Inventory Transactions]![Quantity];-([Inventory Transactions]![Quantity]))
for some reason it does not work. It is not calculating current stock. If i remove stock it shows a negative amount based on the amount i removed, and when i add stock it shows a positive amount based on what i added
I have attached a sample.
[ATTACH][/ATTACH]
Sample.accdb
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
In my table I have the following 4 fields with the associated date field:
Bronze: 11/1/2013
Silver: 5/1/2014
Gold: 11/1/2014
Platinum: 5/1/2015
I am trying to calculate a value based on comparing the current date to the dates in these fields. I am using the below formula. However, using 6/27/2013 as the current date, my formula keeps resulting in "Bronze" when it should result in "Standard" Am I doing something wrong?
=IIf(Date()<[Bronze],"Standard",IIf((Date()>=[Bronze]) And (Date()<[Silver]),"Bronze",IIf(Date()>=[Silver] And Date()<[Gold],"Silver",IIf(Date()>=[Gold] And Date()<[Platinum],"Gold","Platinum"))))
I am trying to get deposit amount for the current month but results are in accurate i am using this
DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
Hi
I'm looking to build a product generator - so user will select from a series of combo box's (Bed, (could be 5000, 5100 etc) Plug,(could be A,B,C,D) Castor could be ZZ, AA, EE,etc) and a code will be generated. (i.e 5100CEE)
I can achieve this - but I've been thrown one of those balls that are curved. The end user also wants a picture of each part of the code to be outputed on the final order form - and here's where I'm struggling - as if I pull from my dumb form (with dumb combo box's) - I've nothing to link to - and if I link to a table, I get zillions of records.
Any advise, general direction, gratefully accepted.
Cheers
Paul
Reet, I have a problem i have been pondering over for the last hour or so and im not sure the best way to do this! Its slightly tricky to explain so bare with me!
So I've made a Software Licensing Database which tracks licenses etc.. Its based information provided by another company which handles all my companies data handling etc..
Ok so in this database it tracks all installs and compares them against licenses. But this 3rd party company doesnt make life easy and sporadically buy licenses thus there's are always licensing issues here and there.. hence wantign the db to keep track. However rather than having a simple 10 licenses, 10 intalls for abit of software, they have created an entity called a Delta which is hand-me-down licenses. i.e.
Adobe Acrobat Pro 8:
100 Licenses | 50 Installs | 50 License Delta
Adobe Acrobat Pro 7:
25 Licenses | 93 Installs | -18 License Delta
So as you can see the Delta is Licenses available for lower versions & editions of software. So where I get these reports on a figures basis my boss wants me to physically give each install a license. Thus thats me breaking down a number of License into a singular form and assigning it to a specific person. Which is all done thats easy enough my problem is Incorporating the delta for that piece of software. So above as you can see for Adobe Acrobat Pro 7 I can happily assign those 25 Licenses to 25 of the 93 people but I dont know how to reference the 50 Delta Licenses on Adobe Acrobat Pro 8 to the remaining 68 people.
Of course the example I have provided makes it seem not to difficult but you have to remember I have about 60 different versions of Adobe Acrobat, they have multiple editions (Pro, Standard, Corporate, blah blah...) Baisically I cannot see anyway of writing abit of VBA giving each bit of software a Hierachial order in which to refer to.
The best possible way of doing this I can see is creating an alternative table which I will have to manually go through the 23K bits of software and define which bits of software are higher up the foodchain & eligible for a 'Delta License'
Any help is much appreciated!
Cheers
Can someone take a look at the attached prgram and see where my error is:
From the control panel.....Click print invoice....enter 100 and then 1000.......for payment status I want to be able to either pull the "paid" invoices or "unpaid"..........I really dont understand why the tag request comment keeps poping up.......This is a program that I have taken and modified.....Tag rest number I do not beleive needs to be anymore.....
thanks in advance
I have a database of equipment that is hired out. When the equipment is hired out the date is recorded and when it is returned the date is also recorded. That same piece of equipment may then be hired out again. What I want to be able to know is what equipment I still have in stock at the base. I have tried many different attempts of trying to get this to work but I don't seem to be having much success. Could someone please tell me how I can do this.
View 2 Replies View RelatedHello,
I am currently designing a stores management database similar to the Northwind database but alot less complicated. We dont need all stock price,sales price and order information. The objective of my database is to calculate stock on hand and have a alert if current stock levels are below a reorder level.
I have taken a begginers course on access and so far so good. Got my relationships sported and everything is working nicely apart from the stock on hand calculation.
What I have is a products form and inventory movement subform. I have a one to many relationship between the two and when you scroll through the products, the inventory movement is updated too. Great.
But now for the stock on hand calculation. I have inserted a formula into the header of the subform and it works fine, but when I do the same calculation on the form I either get a result that doesnt scroll over with each product or I get a #Error.
I'm stuck and have been working on this for a few weeks, if someone could help that would be great.
Thank you,
Kerry.
PS: see attached for description of relationships
Hi all,
I need a hand from your side with the following.
The attached Excel workbook needs to be transformed into an Access 2003 database.
It is a list of clients’cases represented by lawfirms and the amount of money that is involved.
The case comes in under the ‘Aufstellung’ worksheet and can progress to a Claim or 2 different other Requests.
Purpose of the database :
1.Fast entry of new cases
2.Easy look up of existing cases
3.Conform updating of existing cases (warning when a certain case number already exists)
4.Easy report running
5.Keep the entire history of updates, changes, deletions and processing to a Claim
Tables :
1.Aufstellung
2.Claim Request
3.MB Request
4.MFA Request
-Question 1 : In the tables you will see that several different appointment dates and hours for one case are listed in 1 cell. In order to keep the history of updates do I need to create a separate Appointments table ? How would I structure this then ? The unique identifier is the Case Number. But 5 columns (Columns E, F, G, H and K) are ellegible to be treated this way. Do I need to create 5 separate Appointments tables ? And how do link them ?
-Question 2 : In order to split all the packed Appointments information in one cell I need to transfer the column to Word, replace the manual break by e.g. *, re-import the table in Excel and do text to columns ? So that each Case Number gets a number of columns accordingly to the number lines in the original cell ?
Queries :
1.List all Failed cases per form
2.List all Open cases per form
3.List all Filed cases per form
4.List all New Date set by Authorities per form
5.List all No results per form
6.List all ABC per form
Forms :
1.Aufstellung
2.Claim Request
3.MB Request
4.MFA Request
-Question 1 : How do I set up a check box in the Aufstellung form which, by checking off, will transfer the current record to the Claim Request form ?
-Question 2 : How can I make a field to be a lookup field ? e.g. When typing a name, it will give a drop down list of existing names of the table and the possibility to type a new name which will be stored in the table ?
-Question 3 : How do I make a pop-up warning message in the Austellung form when a certain case number exists already in the database ? And how do I block the case number field for duplicate entries ?
Reports :
1.Aufstellung complete table
2.Claim request complete table
3.MB request complete table
4.MFA request complete table
5.List all Failed cases per Form and include count of Failed cases plus sum of the Main demand in Euro
6.List all Open cases per Form and include count of Failed cases plus sum of the Main demand in Euro
7.List all Filed cases per Form and include count of Failed cases plus sum of the Main demand in Euro
8.List all New Date set by Authorities per Form and include count of Failed cases plus sum of the Main demand in Euro
9.List all No results per Form (empties) and include count of Failed cases plus sum of the Main demand in Euro
10.List all ABC per Form and include count of Failed cases plus sum of the Main demand in Euro
-Question 1 : How do I include a count of the records into the report ?
-Question 2 : How do I include a sum of the Main demand in Euro into the report ?
Switchboard :
1.Make a new entry or update an existing one
a.Aufstellung
b.Claim request
c.MB Request
d.MFA request
2.Search
a.Case Number
b.Applicant’s Name
c.Date
d.Court record Number
3.Reports
a.Failed case
b.Open cases
c.Filed cases
d.New date set by Authorities cases
e.ABC cases
4.History
a.Export history table to Excel through e-mail
b.Who updated the record and when ?
-Question 1 : Do I need to create a switchboard at the very beginning of my database ?
-Question 2 : A switchboard can only contain 8 buttons, how do I create a sub switchboard ?
-Question 3 : How do I set up the user access tracking – who updated what when ?
Thanks in advance !
Hi Guys,
You know how you get the form header/detail/footer sections on a form. Well, how can you make text appear down the side (ie so that it overlaps all of the header/detail/footer sections).
Take a look at my drawing, it will explain it better...
Hi
I am creating rent software and I would like to do a running balance in a report.
In the table there is a field called amountPlus and amountMinus
Record one might have $10 in amountPlus making the balance $10
Record two might have $30 in amountPlus making the balance $40
Record three might have $20 in amountMinus making the balance $20
Having both fields is critical as they are displayed in the DR and CR columns respectively on the report.
(yes I know I could have called the fields DR and CR but I am financially stupid).
The report is displayed in date order, but the transactions are not necessarily entered in date order.
How can I do a running balance on a report?
It needs to take the previous balance, and either add or subtract the value depending on if the value is sitting in the DR column or CR column.
Please give ideas.
Thank you in advance
I have a table of three columns (although there are other non-currency columns in this table) but I would like the balance column to show the balance of buget column less total expenditures:
Budget Total Expenditures Balance
Thanks.
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
hi all,
i m having a problem with a simple inventory system. The problem is that i have a query which calculates sum of 'Debit and credit' as 'Balance1' for each row. Now what i want is to add every 'Balance1' to next sum of 'Debit and credit'. e.g.
Debit Credit Balance1 Balance2
a b a+b (a+b)
c d c+d c+d+(a+b)
e f e+f e+f+c+d+(a+b)
I would to generate column 'balance2
Any help would highly be appreciated.
Thanks
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!!!!!!!