stock management application in Access

Question

Hello,
I want to create a stock management application. I have the following tables:
Article(codeArticle, desigArticle, quantity, supplier)
Output(codeOutput, dateOutput, quantityOutput, priceOutput, codeArticle)
Input(codeInput, dateInput, quantityInput, priceInput, codeArticle)

The user of the application must fill in the input or output table according to the operation, and we want these updates to be automatically reflected in the main article table. For example, in the case of entering an output of products, the output quantity should be subtracted from the original quantity in the main table.
Thank you in advance for your help.

Configuration: Windows XP / Chrome 31.0.1650.63

castours · Accepted Answer

Hello
https://www.cjoint.com/?BJjxa56euxb
In this link, you will find an example of inventory management principles.
Operation:
We buy from a supplier and sell to a customer.
When you open the client form, you will find invoice numbers in a subform corresponding to the product sale in the second subform.
To select the product, go to the dropdown list of product numbers.
You will find in this list the products purchased from the suppliers along with their stocks.
If you make a sale, the quantity sold is subtracted from the stock, which will be updated the next time the form is opened.
Now this database can be complemented by invoice states, with selling prices, VAT calculation, etc.
I found this help on the site I suggested a few weeks ago.
I'm not at home at the moment.

Nida · Answer

Thank you very much for your example that showed me how to calculate the available balance for each product.

Since the queries will not be visible if we create a database management menu (with commands to open the forms and queries), is it possible to create a form that is based on this action query that updates the balance?

castours · Answer

for ILHAM Hello Who is the request for?

castours · Answer

Hello You can take example from the database that is online.

grisan29 · Answer

Hello castours the link below is no longer valid, can you provide a valid one (.zip) quote: In this link, you will find an example of stock management principle. http://cjoint.com/?BJjxa56euxb  thank you  Pascal

castours · Answer

Hello the new link https://www.cjoint.com/c/DInxBgxRnOV explanation in a comment above let me know if this model works for you

grisan29 · Answer

Hello castours and the forum
I just opened a post in the Access database section because I have 8 tables that I don’t know how to adapt to queries or forms

https://forums.commentcamarche.net/forum/affich-30790645-acces-a-toutes-les-tables-mais-comment#p30790645
and here is my test workbook, not perfect as it is not finished
http://cjoint.com/?DIonlwqkC4Q

your model seems to fit but I have only opened it for now
but I keep coming back to the same issue with my 8 tables

pascal

castours · Answer

Hello Your link isn't working. Before putting it on the site, compress it into a zip, then make the link as attached .com. It should be in blue,

grisan29 · Answer

Hello Castours,  thank you for your response, but when I edited my reply, the link was blue but not when I submitted it; however, I managed to access it anyway. Here's the new link:  http://cjoint.com/?DIpg0n8a9Jm  same today.  have a good day   Pascal

castours · Answer

Hello
I looked at the links you sent me. They are 2 different databases.
They are tables of equipment selected by category.
Which one with 8 tables do you want to work on exactly?
Sell, buy equipment?
Explain to me the final purpose of your database.

castours · Answer

Hello The test billing database, is this the one you want to operate. For me, I would only create one table with all the products but with a field to distinguish them and sort them later.

grisan29 · Answer

Hello Castours  Thank you for your response. In the link above, there is my billing attempt, and to continue it, I need access to my articles.  The link provided in the other post is an external attempt to integrate my 8 tables; the goal was to include it in the attached workbook above. The article tables mainly serve me to fill out quotes or invoices, that is to say, to sell. The final goal would be I create quotes ranging from 1 to several pages using my 8 tables, one of which is for services. I write a section and/or a comment separating the parts of the quote. I write a subtotal at the end of each part. I can move up, down, or delete a line. I can create an invoice alone. I can create a down payment invoice. A paid invoice. An invoice based on a quote. An invoice with variations. Save all created documents in an archive for easy retrieval and reopening if necessary. Manage invoice/quote numbers. The quote number will be separated from the invoices since a quote may not necessarily be closed upon the issuance of an invoice. Down payment and paid invoices will follow the same rule as regular invoices. I also write the site address if different from the billing address.  In short, there is a lot of work to do. Pascal

castours · Answer

Hello
I’m sharing a link where the example I sent you has been modified for stock management.
I imported the tiling table and renamed it T_Products for compatibility with my database.
If you were to make this choice, you would need to combine your 8 tables into one with an additional field called Category, for example, where you would list electricity, tiling, etc. for sorting.
For me, you shouldn't separate your billing from the rest.
You need to carefully think about your tables and the relationships between them.

https://www.cjoint.com/c/DIpkLE9BvFb

grisan29 · Answer

Hello Castours,Thank you very much for the file, I will look at it with a rested mind this evening as I am going to prepare my meal and head to my client.Would this example of feuille.xlsm with all my items be good? http://cjoint.com/?DIpmGiof1Rz Have a good afternoon. Pascal

castours · Answer

Hello No because everything is on the same line. You need to merge your 8 tables into one.

grisan29 · Answer

Hello beavers  So merging means putting them one under the other, I don't quite understand   Pascal

castours · Answer

Hello Yes, that's it, but for each line, please include the category so that we can sort the table.

grisan29 · Answer

re castours thank you for your response, so I think this is what you are talking about  http://cjoint.com/?DIqnvOYs3zD  see you tonight, have a good afternoon to you and the forum  Pascal

castours · Answer

it's perfect

grisan29 · Answer

Good evening Castours,  thank you for your support, but the services section should not be included with the other items as it is not subject to inventory management. How can we separate it, by creating a separate table or in another way within the 8 tables?  I will redo it in the billing workbook if I manage to establish the appropriate relationships, which will be an achievement for a beginner with only 2 months of experience in Access.   Pascal

castours · Answer

Good evening Now that your data is in a single table, you can delete the others. Then create a main form and a subform with the category and articles table.

castours · Answer

Good evening  https://www.cjoint.com/c/DIqv3cvJnlG  Here is a link with the modified database. You have a search form by category.

grisan29 · Answer

Good evening Castours  Wow, that's very beautiful what you did, so in the relationships I've noticed that the separate article tables have no more links, which means I can delete them to lighten the workbook, but what about the states where there is a catalog for each table? Should I create one that groups everything together like the articles, or leave them as is (that's done and quite easy too)?  I would really appreciate the version on your stock calculation workbook   This will be my last question for this post that you have wonderfully handled  It's a shame that the points don't go by tens   Pascal

castours · Answer

Hello
Yes, indeed you no longer need the tables
For the reports, you just need to change the source by using a sorting query.
For example, you can create a query with the articles table and sort by plumbing. You will do this for each category.
For the rest, you need to create a client table, a supplier table.
If you want, you can adapt my stock calculation database as you wish. That way, there will be less to do.
Let me know your choice.

grisan29 · Answer

Hello Castours thank you for your reply, I was just in the testing phase when I found that the dropdown list of the s/f_facture form wasn't working, but everything else should be the same now that the name of the sheet has changed  well, I'll check that later I have to leave I'm in the process of adapting your stock calculation workbook  I'll be back a little later  Pascal

castours · Answer

Hello
If you agree, I will try to adapt stock management with the articles table.
To avoid starting from scratch, I will rename T_Articles to T_Produits, as well as some field names.
Can you create a T_fournisseurs table with the fields: N°Four (Primary Key), NomFournisseurs, and complete it with the suppliers corresponding to the items in the table? Just send me these 2 tables.
In the articles table, add a N°Four field as a secondary key to establish a one-to-many relationship between the articles table and the suppliers table.
This will make things easier going forward.

grisan29 · Answer

Hello Castours thank you for your encouraging response to create the supplier table, I have several but some sell the same thing, however I depend on the client's choice to order from one supplier or another well I'll see what I can do here is the link to the supplier table http://cjoint.com/?DIsmlNpbpQF the t_nomfour table in creation mode gives approximately the list of what they can sell me depending on the client in the optional description field I renamed the articles table to T_produits with a field for supplier number I haven't made a relationship with a foreign key (what's that) thank you in advance  Pascal

castours · Answer

Hello  another application to modify for your case  https://www.cjoint.com/c/DIsstwh0h35

grisan29 · Answer

Hello Castours Thank you very much for this spreadsheet, after a quick overview I found 3 identical bugs on 2 queries and one report, “the field “[TVA]” can refer to multiple tables listed in the FORM clause of your SQL statement” Following the bug, the implicated queries are: R_CalculPrix; R_CalculPrixProduits and the report E_Factures I will also integrate my clients but I need to modify the table and the rest involved to add the fields “to the attention of:” and “address complement” Besides that, I will take a closer look at the spreadsheet and integrate my items after my meal Pascal

castours · Answer

I am working on the stock calculation database to create an invoice statement.

grisan29 · Answer

Hello Castours Here is where I am with the integration of my article table. The hardest part is figuring out how to create identical relationships to the old ones, as I haven't found in the types of joins how to make a connection between two tables with a "1" and an "8 extended!" I'm only making straight links, see the following link I modified the client table to add attention to and address complement  One small thing, I read on several Access links/forums that it is not recommended to use accents and spaces, what is the situation? http://cjoint.com/?DItizDWfuWW Pascal

grisan29 · Answer

Good evening castours thank you very much for the new binder you linked, but I will open it later, just wanted to say that yes PUV is Excluding Tax? well after trying to open everything there are the same issues with the field "tva  Pascal

grisan29 · Answer

Hello Castours  Thank you for your response  You say "Do you add your profit margin to the PriceIncludingTax?"   The price excluding tax that I have is the public price excluding tax and does not include the discount I have with my supplier because the offer he provides me includes both prices  I do resale when issuing an invoice, since the estimate is only for informational purposes for the client about what it will cost him, but once the estimate is approved by the client, there is a deposit invoice that gets printed and this deposit is deducted when creating the invoice based on the estimate  I will get back to you with your workbook as soon as I have integrated the supplier numbers, as it is time-consuming to enter the numbers for 794 lines  Here are some explanations, and here is the file  http://cjoint.com/?DIuqIeDrPTe on which I added a table T_Articles because on the table T_Produits at the end of each line there is a + that I cannot place back   Pascal

castours · Answer

Good evening
https://www.cjoint.com/c/DIvaDoKTHMT

I went back to the first version I adopted. Check the stock functionality.
When a customer buys, you select a product with N)Prod. A dropdown list opens and you choose from the list, and the stock is displayed.
You enter a quantity, save, and close the form.
On the next sale, you will see the stock has decreased.
For testing purposes, I randomly assigned some N°Four.

Try to get the supplier and order to work with the main menu.
Give me your feedback.

castours · Answer

Hello Have you done something on the latest version of the database? Can you tell me which region you are from? I am from Touraine.

grisan29 · Answer

Good evening CASTOURS and the forum  I think I'm going to leave it at that and continue with my current file to make my quotes/invoices Pascal I don't know where to mark resolved  Pascal

castours · Answer

Hello if you want precise help, but your database on the site with cijoint.com

grisan29 · Answer

Hello Castours Thank you for your response, but is it reasonable for you to get involved like this? I'm sending you the file, but don't rush https://www.cjoint.com/c/EDbitif8vyz  Pascal --

castours · Answer

Hello If your quote is accepted as is, it becomes an invoice. However, we need to produce two different statements. On the statement you made, you should include a logo with the company's address. Consulting or issuing an order For you, is it purchasing products from a supplier? Or ordering for a client?

Stock management application in Access

39 réponses

Texture issues in enshrouded

Where to find 5 mechanics points? sims 2 ds

How to remove a stuck disc from ps3?

How to digitize my vinyl records

How to switch from creative mode to survival in minecraft?

Unknown debit card charge escot

Screen brightness issue - acer laptop

The sound is crackling on windows 7

Convert .wlmp to .avi

The sims 2: cheat code "maxmotives"