how to project this db
Hello need to manage my small business. Every day X products goes out from my store to my clients, and Y products return (my client has not sold them all). So i think a table for products i sold, and a table for clients. Then my doubt is how to manage the sold product and the returned products Shall i do 2 different tables, one for sold one for returned? That means that i wil have a table with
date client product sold quantity
and the other with product returned insted of sold
At the end i must be able to see the overall product sold/retruned by clients and percentage
And also to see by products the relation sold/returned
For this i see the report/graphic available don't allow me to get data from 2 different tables..so how can i do that?
101 replies
-
Been a while since I built an inventory/sales DB but here goes. When I looked into building one in Access this was rule number one.
Never add the quantity you have in stock as an entry into a field. Stock balance is a result of Item coming in minus items going out. That is to say:
OnHand = StockIn - StockOut
Stock in of course could be purchase orders, or refunds/credit/return and a choice field would be used to define the type StockIn.
The rest is pretty much what you want you specifically require. To use 2 tables, one for outgoing and one for incoming is no ideal.
Hope this helps you on your journey -
thanks..first thig i don't have a stock..it is fodd which is prepared every day and every day is delivered. So it happen that we sell x product to market Y and the next day will get back z products from that market. This product are not going in stock..they are thrown in the bin
Itried to use 1 table..where i have (among other) 1 field for SOLD (where i ll insert the product) and naother field RETURN with another field...they never get poplaed in the same record though
So it happens that each record will have an empty field..either the sold or the returned..and this cause problem when doing report though
-
off the top of head, I would suggest:
1) a catalog table that tracks your daily production of your items. this would mean there would be a subtable, i.e. catalog items that allows you to track what items were produced per day. You probably produce the same items most days so you can create a button to take care of entering the same items.
2) an order table that tracks what you send out to your clients. this would also have a subtable, order items. It is in order items that you can track what was sent and what was returned.
3) then you could create a dashboard that brings together the two tables to show you graphs and tables of your business.
-
the point 2..how to make the difference send/returned?
I m doing this now
A table where i choose the client (from another table)
a subtable of this table where i select the product and quantity..with a radio button to change between sold/returned
Could be fine?
But the problem now is the report...need to write code to have a custom report, for what i see, to select from table certain field, to check the radio button for sold/return...
-
stef. idea said:
a subtable of this table where i select the product and quantity..with a radio button to change between sold/returnedI see it as two different fields (sold/returned) in the order items. In the order table you are tracking data for what happens to a particular item that was sent to a certain client on a specific day.
I’m sorry that I can’t be more helpful with the graphing part as I don’t do any so hopefully someone with more knowledge than me can jump in.
-
this is the way i started..each records have the sold and the return field, but of course only one will be populated. I got some difficulties at time of reportin thoug..as the graph woul show me the record with a NULL field
-
imagine there are 10 records. 8 are sell and 2 return. Even if i put a 0 value in the sell field of this 2 record, the report will count 10 record, like 10 selll..or at the opposite 10 return. how to get out of this?
-
stef. idea said:
imagine there are 10 records. 8 are sell and 2 return. Even if i put a 0 value in the sell field of this 2 record, the report will count 10 record, like 10 selll..or at the opposite 10 return. how to get out of this?That is a problem when you are working off of your raw data tables. You should look into the Dashboard concept. The idea behind it is that your raw data tables are good for just keeping data. Once you need to start evaluating your data it is time to move to a dashboard.
Here are two videos:
I've mocked up what it could look like in the attached DB.
-
thank you
tomorrow i ll watch them and ill come to you again ! :)
-
i have loaded the inventory..i was working at it already, being a template. I start modifyng it...and add the field of returned...don't know if i am doing good..though
There is still somehting i miss, in understanding the concept
-
in client or purchase order?i'll check it better
-
I'm sorry, I didn't give a little direction.
The Quick View table is a dashboard, that is incorrectly setup. When you open the table, click on the first record. Here you can change the Month and the Total Produced, Total Shipped, and Total Returned with change.
The Graphs table has a Bar view where you can see bar chart of Total Produced, Total Shipped and Total Returned by Months.
-
are you talking about a specific table?
-
hi @fred i am trying to follow your info but don't get much
First i don't find where is the returned field. Also i don't understand what you said aabotu the QUick View Table that is incorrectly setup. 1st thing..where is th equick view?you say "when you open the table"..what table are you referring to?
-
did you see the file i uploaded to a post earlier? download the file then import the DB.
-
aahhh sorry..yes i have seen it, and i thought i imported..but i was wrong i imported the inventory template i had exported before! :) ok i'll have alook now! thanks
-
stef. idea said:
where is the returned fieldmore background:
The Products table is where you have a record for each item you could make in a day.
the Catalog table is where you create a daily record of what you made that day, linked to the Products table.
The Orders table is what you send out to your customers. There is a record for each day for each customer that got sent items. So you will see it here where you can enter the returned items.
-
seeing it..just i don't really understand what i need catalogue for
COuldn't i make the order and inserting just the products straight away?
With catalog i should first insert the product i have prepared tody in th ecatalogue, right? that would mean working more time at the db..
We donb't prepare more food boxes than what are needed..we know that each day we need to prepare 100 boxes(example) and 20 go to x, 30 to y and 50 to z..so what is prepared is what goes out
The idea is that at morning before th eoperator goes out, we scan with barcode all the product we have prepared, scan thge client code, then scan all the product for it. Then scan naother client barcode and scan all the product we have prepared for it ans so on. The operator when at the client..agai scan the client code and scan the returned products...
-
modifyng the inventory that come as template i have reached, in my opinion, more or less what i need
The problem is to analyze data...I have all the sales in table with subtable (tale contain the client and subtable the movements
Now how do i d oif i want to see how many products X the client 1 has bought?How many x has retrun?percentage sold/return? with the report that are already there i cannot achieve this
-
stef. idea said:
seeing it..just i don't really understand what i need catalogue forThe catalog is a way of tracking what is available to customers. It might fit better with a more durable product (cars, lamps, etc). It doesn't always fit all situations. It is a way if you want eventually want to have the ability to track parts (or ingredients in your case) that make up a final product you sell, but that leads to a bigger discussion.
Another way for you to use it is for planning. You as the boss can use catalog to plan ahead. You can enter in records that have the next day or even special days ahead and then others in your company can then see what is coming.
stef. idea said:
The problem is to analyze data...I have all the sales in table with subtable (tale contain the client and subtable the movementsTake a look at the Quick View table in my example to see how to do a sum().
-
ok so i think i don't need the catalogue. But for the report i 'll need to write code? For example select all record from client X froma dat a to date be, marked as sold
Then the same but marked as reurned
Then compare the forst and the second..something like that right?
Content aside
- 1 yr agoLast active
- 101Replies
- 508Views
-
3
Following