I have created 3 tables for simple inventory.
a) Item details
b) stock in
c) stock out
All I simply want to do is create a new table by item id to show balance of stock.
I have created queries from Incoming Raw Material and Out Going Raw material Tables..Now how to get balance Stock of Item wise,
My Query Look as under
item wt In Coils Out Coils Wt Bala Stock
part A 100 kg 10 3 30 Kg 7
part A 100 kg 10 5 30 Kg 5
I want that Balance Stock shall be 2 Coils of Part A , How shall I get the same result.
I'm creating s simple database to track my printer toner supplies, what i want is that..every time I select a date (using date picker) it will automatically deduct 1 from the total number of toners on stock. Right now, i have to input 1 in a field and it will update the remaining toners.
View 1 Replies View RelatedI have a table composed of the following fields:
1. Entry_ID:
2. Item:
3. Previous_Balance:
4. Increase_Or_Decrease:
5. Current_Balance:
Once the selected item had been entered, i want the Current_Balance of previous entry of that item to be forwarded automatically in Previous_Balance field. Is it possible? I have at least 100 types of items.
I'm currently working on fixing an older 97 database that I've updated to 2010. I have just populated the Drop down box with about three fields. Ideally what I want to do is after having selected the item number from the drop down box I then hit the button that creates a report with the information about that item number.
At the moment...If the box is blank it reports all the item numbers, however if i fill the box(select an item) it returns nothing i.e the report is blank.
I've looked at the query that builds the report, there are only three fields that populate the. location, part number and description. from a tbl called MainDetails
the only other thing:
If([Forms]![frmReports]![FLoc] Is Null,[FUNCTIONAL LOCATION] Is Not Null,[FUNCTIONAL LOCATION] Like [Forms]![frmReports]![FLoc])
it has a criteria of <>False
*FLoc is the drop down box *functional location is the location field.
I believe all the above does is populate the report if Floc is empty.
What can I put to make the report generate what ever I pick in the drop down box 'FLoc' source the three fields from the 'MainDetails table'.
Basically I am inputting stock into my database so I can have a record of what I have left and who it's gone too. Where I'm stuck though is when I want to add an item that I have more than one off but only enter it once and have it assigned a unique stock ID number (this is in the form of an autonumber) for however much quantity I choose to enter. So for example I have 5 keyboards, I type in 'Keyboard' into my item field (named txtItem) and then type '5' into my quantity field (named txtQuantity). When I then press Add Stock (named btnAddStock) I should then see in the table, 5 separate Keyboards listed each with a different Stock ID number.
View 14 Replies View RelatedI created a report to show stock level and transactions. there are date, transaction, quantity and balance fields. the balance field show the total balance of the item at the moment. what I need is to show the balance at the transaction date.
View 7 Replies View Relatedi have a production database in which i have different sections processing on the same product. i have a daily production entry form on which datewise entry is done for each section.
i want to maintain the total stock of each section (sectionB) with productName, input from sectionA , production from sectionB, balance (input-production) . presently i used query for this .. but i need a stock table in which entries should be inserted by an automatic query .. how should i achieve this :
1. by an append query who runs every time to append the quantity when a daily entry is made .??
2. by an update query to replace the quantity with a qty from totals query ?
i.e. what is the best method to maintain inventory from input & output tables ?
I am planning my new DB and am contemplating the best design. It will be used for warehouse stock rotation and control of pallets. I want to track each pallet (product/time in/time out etc) to each pallet space within the warehouse. There are a total of 400 pallet spaces or 'locations' as i will refer to them as.
Now, would it be possible for me to have a table set up for each location? Will access object to having 400 tables in my data base?? Is there a limit?
I did using below:
One table with Student Basic Records with Course name and Total Fee
Tabel name Student_Rec and Fields are below
ID, StudentName, Course Name, RollNo, Total Fee
then another table name Fee_Details for receiving dues in installments.
ID, RollNo, FeeDue, FeePaid, Comments
Now i want the FeeDue Field should show the Balance feedue of each student
I mean it automatically check total fee due from student_Rec table and also check all previous entries for the same rollnumber in Fee_details Table and show the pending amount as FeeDue.
I want to perform in this query that Opening balance + Dr - Cr = Closing balance.And the next day my opening balance will equal to the date() -1 closing balance. And when todays movement DR and CR take changes it will also take affect on closing balance.Here I have two questions 1st is how can I built a qry that will make todays opening equal to last day closing . Can I have to built two queries or make another table that store last day closing then create relationship and again built a query that perform.
View 14 Replies View RelatedI'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 have multiple fields in a table that are set up as Yes/No and display as checkboxes on a form. Now I need to create a text field on a report that showes all the Yes answers. The text field needs to figure out if it's a Yes answer, then display the field name, if there is more than one yes answer in the fields it is looking at, it should separate each with a "," . This is a large text field, that could show as many as 10 Yes answers.
What is the best way to go about doing this? I use Access 2010
I have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?
I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe
Product A - CustomerADiscount - 10%
Product A - CustomerBDiscount - 20%
Product B - CustomerADiscount - 5%
Product B - CustomerBDiscount - 7%
This link these values up within a query? Would this be the best way of doing it?
Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?
I have problem to create Stock query in Access 2013..I want to sum Purchasing numbers base ItemName in Purchasing table by query .. but i found error in summing?
View 4 Replies View RelatedI have a main form with several continuous subforms on it. These subforms are based on a selection of fields of a complete and editable form, so these subforms are overview forms so to speak. Whenever a field of a certain record is clicked, the other subforms show the linked data for that record, whenever a field of a certain record is double clicked, the corresponding form opens so data can be edited.
I would like to show which record is selected (record selectors are not enabled) in the way you select text in any application in windows. So when record A (holding fields A1, A2, A3 and A4) is selected in any way, the text should be lit up as if struck with a text-marker pen.
How do I go about that?
It would really be nice, since there is a cascading effect of records going on in the main form, which can get a bit confusing if you have to or want to glimpse at which record is selected. So for ease of use, this feature would really be nice.
I am new to Access. I am creating a simple order/payment database.
I have tables for the orders and payments and have a relationship setup.
I created an order form with a subform for the payments.
[URL] .....
I have a project to take. It relates to school fee payments by students. I have created tables, query and forms for the same. The database is able to calculate the Balance using the query. Now my major issue is how do I go about Calculating the Balance for the second fee payment, given that the amount payable on the next payment will be the previous balance. I have tried to think using the nested if to try it, but invain!
View 4 Replies View RelatedHi all
I have a rating table with the rate ID and title. Is there a way to represent the rate as stars image like this
http://65.19.160.24/forums/images/rating/rating_5.gif
http://65.19.160.24/forums/images/rating/rating_4.gif
http://65.19.160.24/forums/images/rating/rating_3.gif
http://65.19.160.24/forums/images/rating/rating_2.gif
http://65.19.160.24/forums/images/rating/rating_1.gif
instead of just showing the title of it.
i tried with this code
Select Case Me.RateID
Case 1
Me.Image0.Visible = True
Me.Image1.Visible = False
Me.Image2.Visible = False
Me.Image3.Visible = False
Case 2
Me.Image0.Visible = False
Me.Image1.Visible = True
Me.Image2.Visible = False
Me.Image3.Visible = False
Case 3
Me.Image0.Visible = False
Me.Image1.Visible = False
Me.Image2.Visible = True
Me.Image3.Visible = False
Case 4
Me.Image0.Visible = False
Me.Image1.Visible = False
Me.Image2.Visible = False
Me.Image3.Visible = True
End Select
but there is somthing wrong it dosnt change ?? correct me if im wrong please
I have made a few cascading combo-boxes that dynamically change based on the data entered into the field above them.. Example:
A SKU is entered, the combo-boxes pull the information for that sku from one or more tables (using SQL Query).
Now... I am having trouble getting these fields to show the first item in the list of possible item specifics. Everything comes in fine, however, I need to form to refresh and repopulate the fields one the SKU loses focus.
Any Ideas?
I hope this amendment to the code for the file attachment that doing a cumulative collection of the items purchased, but only "product code and place of storage," where the code works efficiently with the condition class code but I can not be modified to work two conditions together.
View 2 Replies View RelatedI need a (group by?) query that shows my items only by the last updated price by date.
Basically i need to show the last date. (MaxDate?)
I keep a database for my restaurant of my food items and change their price everyday. i now have a table that archives individual items by date, price and company in a subform.
Item_id, Item, date, cost , unit, Company
1 asparagus, medium8/12/2005$1.45poundEuro Gourmet
1 asparagus, medium8/13/2005$2.00 poundEuro Gourmet
55 oil, blended 8/12/2005 $4.99 gallon accardi
116 polenta 8/12/2005 $1.58 box accardi
I am trying to kick out an item or inventory sheet by creating this query but i cant seem to group by last date. i have all my items showing up multiple times (as many as i entered)
i have been easily able to create an inventory report off a query i used with only one date that i would change. but now i want to archive prices for charts.
i have tried max dates on item_id fields and date fields and nothing seems to work.
Thank you
Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.
1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.
2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.
I have a form with a combobox whose list items are taken from a table of currencies (called tblCurrencies)
Row Source = SELECT [tblCurrencies].[Ccy] FROM tblCurrencies ORDER BY [Ccy];
Problem is, there may come a time when the currency they want to select is not yet part of the tblCurrencies table, and needs to be added. For simplicity, I'd like to have an additional option in the dropdown (perhaps at the bottom) to specify a new currency.So an additional list item (e.g. 'Add New...') which when selected could prompt a subform where the user could specify the new currency (and any other relevant details specific to that currency) which can be programmatically added to tblCurrencies.how do I get that extra option into the list items for the combobox?...
I have two tables. The first one is as follows:
MP No (Primary key), Sales, Date Entry, Specs, Email
The second Table is as follows:
MP No, Quantity, Description, DWG, Price
There is a "one-to-many" relationship between the two tables through the MP No field so whenever I go to the first table there is a sub-table for each MP No. Correct?can I create an extra sub table under the sub table already created?
I've done a YouTube Video, showing 3 different methods of posting your table structure to a forum HERE:- MS Access - Copy Table Structure for Forum
Here is a Google Doc showing the first procedure of the three:- MS Access - Copy Table Structure for Forum (First Method)
Here is a Google Doc showing the Second procedure of the three:- MS Access - Copy Table Values for Forum
Here is a Google Doc showing the Third procedure of the three:- MS Access - Copy Advanced Table Structure for Forum