I am trying to calculate the stock level for my spareparts. I am aware of that I am not to update the stock level in my table but calculate the total from the transactions.
View RepliesHi,
I'm in over my head with something I've been asked to do at work as my access skills are pretty basic.
I need a sample database containing PRODUCTS and SUPPLIERS, that lets you book IN stock received from the supplier (and adds to the stock level) and lets you book OUT stock issued to staff (and reduces the stock level)
I'll have a go at reorder levels, form design, stuff like that myself, but I'm having trouble with these very basic requirements.
Does anyone know of a sample database that I can download to learn some of this from?
hey, is there anyone who wil be of help to me by creating me a quick and simple database. It needs to keep track how many spare parts I have. Therefore it needs to show how many of each spare part i have, and be able to update the stock levels with a submit request page that when the submit button is pressed the stock levels automatically update. the information required for each spare part is article (what the product is from), article number ( the unique article number of the product), colour (the colour which its from), spare part (what it actually is) and quantity.
Any more questions please ask
thanks
I have been working on a stock database for my company. It needs to hold stock data, Any adjustments in the storesperson may do (QtyIn or Out). And allow Customer PO's to be raised (just to show prices and quantites sold to customers)
I have 4 Tables:
tblStockList
PartNumber (Primary Key)
Description
Location
StockCheck
StockListUnitPrice
[code]....
I then want to create a query to work out the total stock.
At the moment i have two queries, one to Sum the adjustments and one to sum the PO's. (The stock is not removed from the Customer PO until invidual line is shipped)
I then need to combine both these queries to work out the total stock value.
There will always be a value in the Sum of Adjustments but not in the Customer PO. So when i run the query to =SumofAdjustments - SumofCustomerPO's, if there is no record in the SumOfCustomerPo's, no value will appear.
I need to create distinct transactions for each invoice, so that if the static data changes, such as rates of charge, it will not automatically update any history, where a historic rate has been charged.My current thinking is that I create a table and effectively archive all invoices that have been created, as they are produced.
View 4 Replies View Related...you do this:
There was question asked on the forum a couple weeks ago about setting an event reminder that always triggered at a certain time period. Then I got to thinking about it, because I never use the timer function in Access. So I started experimenting, at first it started out with just triggering a message box. Of course the code would stop until I clicked ok. Then I set it up to run a Beep instead of the message box function. And from there I started wondering if I could get it to play an MP3.
So I used the shell function to call a specific MP3. But you can't call a non-executable - at least I couldn't at first try. I thought since the file type was associated, it would work, but it didn't. So then I called windows media player, but it doesn't autoplay when opened as a stand alone program. So then I called WinAmp, and found that when you put in a specific filename in the shell call it will automatically play that mp3. Then I thought, why not make this thing a full blown alarm clock? Ooooh, bad idea, it always plays the same song, and I certainly don't want to feel like I am in the movie Groundhog Day. Aha! I can call a playlist in winamp. Excellent, and since I have random play turned on in Winamp it plays a different song off the list when it's opened. The next problem was, what do all alarm clocks have? A snooze button. So I built a snooze button into it, and then ran into my next issue:
Winamp runs independently from Access, so closing it could be a real pain if I was to have a snooze button. Oooh, but I could put it in as an activeX control? Well, I couldn't find an easy way to do that but windows media player is easily put in as an active x control. So I did that, and whenever the form it is in gets opened it automatically plays. Rock on. Oh crap, I'm back to the specific song again in the properties of the wmp control. So then I thought hey, it's got properties, I can change them when I call the second form to be opened. I have to build a list, and select a song from it. No, I don't want to do that, that would be a pain, and be too much busy work of inputting all the songs I want to choose from. Ah, but I can take my playlist, save it as a text file, tweak it, and import the filenames to a table in the database. Give each one a unique ID number, generate a random number, open the recordset (i know, I could have used Dlookup, but I never messed with it before) where the unique ID is equal to the random number generated, write the filename to a string, and when I open up the form with the player I set that attribute in code and voila my musical alarm clock. Oh, and when I hit snooze, it regenerates the random number and plays a different song for the next time it goes off.
Wow, how pathetic. What's worse, I keep using the laptop as an alarm clock.
I hate you all for making me learn something. :D
I need to create a report that has three labels as a heading on one level and then it will display the rest of the headings on another level.
View 2 Replies View RelatedI have a question about user level security / access control.For my database I have a back-end containing the tables and a front-end with forms, queries and everything. I have set up the access user level security.Dependent from the access level of my users, some front-end options will be hidden and some queries retrieve only part of the data. For example: I have a form to calculate clients' end prices. A vendor can only see prices for his own clients, not for other vendor's clients.
BUT: to make the query work, all users need access to the tables and the back-end must be stored in a public place, right? Now all vendors can just open the back-end and see all information.Maybe protect the BE with a password and store this in the queries?
I have a database whereby users already have to sign in with a username and password, using a form which them allows them to view a front page. The front page allows them to access data and add/amend it.
I now have to adjust this setup as we have directors who want to view the system, but we don't want them to amend it.
Is there anyway to change the current setup that I already have or do I have to scrap it and start again?
How do I use Stock Tickers in access?
for example:
AEPFX
ARTQX
BVAOX
Cash:0.000%
EIBLX
SVAIX
FSIIX
FTBFX
FMIHX
HACAX
HLEMX
JFLEX
JSHIX
WOBDX
[code]....
I have created a database and made the steps to the user-level security and VBA protected the project but i lost the passwords and the MDW file.
is there any way to reset the VBA pass then reset the security level?
this is very important for my business it's a school database with lots of info and it's critical for me to update or at least export some reports and forms to a new project
What is "row level locking"?
How can I get around the "encrypting with a block cipher is incompatible with row level locking" error.
I've never had this error before and I do not know what I did to cause it.
Dear Reader
I am a realtivly new user and I do not know alot of things about Access
I have set up a database, and within this database, i have a table with my product details (the products are books, details include: ISBN, Title, Stock Level, and Price), I also have a table with customer's information (Name, Contact details, etc). I have created an order form, from which a user selects the customers name from a drop down list, and then he selects his name, and finally the date. Following this the user then works within a subform.
This subform has the following details, Order ID, Book ID, and Quantity Ordered. After this I want there to be a subtotal. I have no idea as to how to set this up, but I am assuming that I would need a query or something, to look up the price of the books, and then to multiply it by the quantity that the customer wants. Following this ideally I would like a small box on the main form saying the total value of the order.
Would their be anyway, to add the book ISBN number into the subform, where the user could either type in the ISBN number or select from a combo box the book title, but either way, both fields are updated, what ever method is used.
From this subform, I would like to be able to print out an order summary/invoice. This would contain all the books ordered by the customer in the current transaction, as well as the customer’s information, and the total of the order.
And my final question is probably the hardest one. Is their anyway (possibly by pressing a button) to automatically minimize the stock value on the products table, by the quantity chosen by the user in the order form? And if the quantity wanted is greater than the amount in stock, for a message to appear on the Order Summary/Invoice stating "On Order"
Using the above method, would it then be possible through the creation of a query to show what customers have ordered in the past, in a tab on the customer's details form.
I have attached a copy of the relationships, as well as various images, the order form, customers’ details and product details forms.
Thanking you in advanced
CJM
*If more images are needed feel free to ask
Order Form
http://img32.picoodle.com/img/img32/5/11/30/f_Untitled1m_9a2e33a.jpg
Product Form
http://img34.picoodle.com/img/img34/5/11/30/f_Untitled3m_2e1c239.jpg
Customer's Details Form
http://img01.picoodle.com/img/img01/5/11/30/f_Untitled4m_0b196eb.jpg
Relationships
http://img31.picoodle.com/img/img31/5/11/30/f_Untitled2m_cf8ac06.jpg
i have a production database application in which there are many sections. every section's product stock is generated by running a complex query.
now when i want to add the result from different sections into a one combined stock , i could not design the query as it become too large and on running it produces the error "cannot open anymore database".
i think reason might be too many queries executing at same time .
so do i need to create a table from the stock queries of each section? and then add the tables to generate the combined stock query ?
In MS Access i want to be able log jobs and be able to assign my stock to the jobs i have logged is this possible ? how to create a database to log jobs but how can i make it so when i add stock to the job it will remove it from my stock pool and assign it to the customer thus taking it out of stock.
View 3 Replies View RelatedIm building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.
My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.
I have created a stock control system where i put stock that come in into a specific location i can put the same item over several locations depending of the size of the location or i can can put more than one product in a location .
The problem im having is when i do a order that say remove half of all the stock in it is split over more than one location i cant seem to get it to remove the stock from the location to show space available.
i 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 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'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 RelatedCalculation I am finding incredibly tricky. I am looking at peoples memberships, so have data structured like this:
Person ID Membership ID Membership Start Date Membership End Date
112233 98765 01/01/11 31/01/11
112233 12345 01/03/11 31/05/11
112233 67890 01/05/11 31/05/11
So, people can have had more than one membership.
What I am trying to find is the difference between the start date and end date of the memberships, and see if any were overlapping. So in the example above I would like to start at the first membership and compare this to the second, so the answer is 1 month difference between end date and next start date, then compare the second to the third, so the answer is -1 month between end date and start date.
I'm guessing it's a syntax problem.
This one doesn't give me an error, its just blank:
=Count("[QueryName]![QueryField]='String'")
This one gives me an Error. It's supposed to calculate a percentage. [ReportField] is working properly:
=[ReportField]/Sum([QueryName]![QueryField])
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, folks.
I have to write a PHP web front-end to a client-server MS Access applicatin, and I think I've hit a stumbling block.
I use ADOdb as my db abstraction layer, and it has transaction functionality built in. I've tried it with Access, and while according to the output of the code the transaction has worked, I see clearly that it has not worked.
If I insert an intentionally malformed statement as part of the transaction, none of the other queries are rolled back upon the failure of said transaction.
I know this is an Access forum, not a PHP forums, but I've gotten zero response to this post on PHP forums.
Does anyone know if there's a way I can implement transactions for Access via PHP?
Cheers and thanks very much in advance.
Pablo
Hi there,
I have one table, called tblTransactions, which contains records on security market transactions. For each buy and sell order, respectively it contains one dataset with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).
The following SQL query code gives me then the composition of my security portfolio as per any arbitrarily chosen date (here 1/10/2007):
SELECT [tblTransactions].[Ticker], sum([tblTransactions].[Quantity]) AS TotalQuantity
FROM tblTransactions
WHERE [tblTransactions].[Date]<=#1/10/2007#
GROUP BY [tblTransactions].[Ticker]
HAVING sum(tblTransactions.Quantity) >0
ORDER BY [tblTransactions].[Ticker];
For instance, for some trades like the following...
Data --- Ticker --- Quantity --- Price
01/01/07 --- AAA --- 50 --- $50
01/01/07 --- BBB --- 25 --- $75
01/15/07 --- AAA --- 30 --- $60
...it results in the following query results:
01/10/07:
Ticker --- TotalQuantity
AAA --- 50
BBB --- 25
01/20/07:
Ticker --- TotalQuantity
AAA --- 80
BBB --- 25
However, I would like to add a column to this query, which gives me the number of trades for each particular stock. For instance, the query result based on the sample data above should look like follows as per 01/20/07:
Ticker --- TotalQuantity --- NumberOfTrades
AAA --- 80 --- 2
BBB --- 25 --- 1
I have absolutely no idea how to achieve that. I would be very happy for any suggestions.
Best regards,
JapanFreak