0

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

null
    • Alan_Cooke
    • 1 yr ago
    • Reported - view

    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

    • stef_idea
    • 1 yr ago
    • Reported - view

    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

    • Fred
    • 1 yr ago
    • Reported - view

    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.

    • stef_idea
    • 1 yr ago
    • Reported - view

    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...

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    a subtable of this table where i select the product and quantity..with a radio button to change between sold/returned

     I 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.

    • stef_idea
    • 1 yr ago
    • Reported - view

    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

      • Fred
      • 1 yr ago
      • Reported - view

      stef. idea the way I was thinking about it that you would eventually fill in a number for returned even if it is 0. you can have the field default to 0, so you only have to enter in a number for item returned.

      • Alan_Cooke
      • 1 yr ago
      • Reported - view

      Fred Would it not be a good idea to have one field for QTY with a choice field to set sell/return with sell side have trigger after update which changes the qty to -n

      • Fred
      • 1 yr ago
      • Reported - view

      Alan Cooke It could be.

      My thought was that the Order table and its subtable, Order Items, just tracks the product shipped to and returned from a specific customer on a specific day. I don't think it is necessary to differentiate with a choice field.

      I shipped out 5 item1 to Customer 1 on 5/12/2023 and they returned 2.

    • stef_idea
    • 1 yr ago
    • Reported - view

    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?

    • Fred
    • 1 yr ago
    • Reported - view
    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:

    video 1

    video 2

    I've mocked up what it could look like in the attached DB.

    • stef_idea
    • 1 yr ago
    • Reported - view

    thank you

    tomorrow i ll watch them and ill come to you again ! :)

    • stef_idea
    • 1 yr ago
    • Reported - view

    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

      • Fred
      • 1 yr ago
      • Reported - view

      stef. idea there already is a field for returned.

    • stef_idea
    • 1 yr ago
    • Reported - view

    in client or purchase order?i'll check it better

    • Fred
    • 1 yr ago
    • Reported - view

    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.

    • stef_idea
    • 1 yr ago
    • Reported - view

    are you talking about a specific table?

    • stef_idea
    • 1 yr ago
    • Reported - view

    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?

    • Fred
    • 1 yr ago
    • Reported - view

    did you see the file i uploaded to a post earlier? download the file then import the DB. 

    • stef_idea
    • 1 yr ago
    • Reported - view

    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

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    where is the returned field

     more 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.

    • stef_idea
    • 1 yr ago
    • Reported - view

    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...

    • stef_idea
    • 1 yr ago
    • Reported - view

    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

    • Fred
    • 1 yr ago
    • Reported - view
    stef. idea said:
    seeing it..just i don't really understand what i need catalogue for

    The 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 movements

     Take a look at the Quick View table in my example to see how to do a sum().

    • stef_idea
    • 1 yr ago
    • Reported - view

    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
  • 445Views
  • 3 Following