Consulting

Results 1 to 12 of 12

Thread: Small Database PIM & MDM, help needed!

  1. #1
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    5
    Location

    Question Small Database PIM & MDM, help needed!

    Hi VBA Experts,

    I am a new guy here.

    My boss asked me to think/create a small database for our start-up business. I'm an electronic engineer and have no clear idea how to do that, as Google search indicated there are so many possibilities. The first thing in my mind is using Excel to make one to solve the current problems. Does anyone of you experts could give me some advice? A template would be highly appreciated

    The database has two parts, one should something like a Production-line Information Registration, and the second part should be a database to collect large amount of technical data generated by customers machines everyday. The second part is the major issue and the data would be used for post-processing based on various queries. I am thinking to use VBA plus Power BI to do the second part. Any good ideas are welcome!

    Many thanks.

    Best regards,
    Ji

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,306
    Location
    Google "dezign for databases"

    The help file that was therein used to be my GoTo for understanding databases

    I DLed Dezign 12.2 (14 Day free trial from the above search._ That help is not what I have

    What I have is in the attached zip, start with the "theory" chapter.
    Attached Files Attached Files
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,275
    Location
    It seems to me that Power BI, or Excel and Power Query should be able to do the job, probably without the need for VBA.

    But, as SamT says, the key is in the design of the data, and also how you will get it from wherever it resides now to the application. A nice relational database is good, but that may take a steeper learning curve than you have the appetite (and/or time!) for at present. If the data is not too complex, if it doesn't have too much repetition (such as product names), if it is not subject to too much change, you might get away with a flat file and process that, and maybe rationalise it at a later date.

    Ver simplistically, but as an example of what I mean, say your production line information looks like this (forgive my ignorance, I haven't worked on a production line, but hopefully you get the gist)-

    Date Product Part No. Enter PL Left PL Operative
    12/01/2020 Item A 01234 12:31 12:43 Dan
    12/01/2020 Item B 01784 12:52 12:58 Simon
    12/01/2020 Item C 76532 13:30 13:32 Moira
    12/01/2020 Item A 01234 14:12 14:42 Moira
    etc.

    We could leave it falt as it is, and it would be relatively simple to report from, but we could also change it to a more relational form where we look at the business, what are the attributes of the business. In this instance, we have products, and operatives/staff (and I would venture that nearly always we have dates). So we could split that data into separate tables alonm=g those lines, such as

    Product tables
    Item No. Product
    01234 Item A
    01784 Item B
    76532 Item C
    35631 Item D
    etc.

    Staff
    Staff No Name Date Join Date Left
    1234 Dan 13/06/1982
    1235 Peter 13/06/1982 18/12/1997
    2341 Simon 23/06/2002
    8971 Moira 24/07/2018
    etc.

    Dates
    DateKey Date Month No Qtr No etc.
    20200101 01/01/2020 1 1
    20200102 01/01/2020 1 1
    20200103 01/01/2020 1 1
    20200104 01/01/2020 1 1
    20200105 01/01/2020 1 1
    20200106 01/01/2020 1 1
    20200107 01/01/2020 1 1
    20200108 01/01/2020 1 1
    20200109 01/01/2020 1 1
    20200111 01/01/2020 1 1
    20200112 01/01/2020 1 1
    20200113 01/01/2020 1 1
    20200114 01/01/2020 1 1
    etc.

    and then the main table would look like

    DateKey PartNo. Enter PL Left PL Operative
    20200112 01234 12:31 12:43 1234
    20200112 01784 12:52 12:58 2341
    20200112 76532 13:30 13:32 8971
    20200112 01234 14:12 14:42 8971

    It might be that your data is stored this way in the data source, but if not Power Query could easily disassemble it to be structured like so. But what is the point of all that work you might ask?. the relational form is a much more efficient storage mechanism, which is more important for modern in-memory databases - you can see that we only store each name once, in the Staff table, not many times (and of course, names are usually much longer than my simple examples), and the numeric keys we use are far less memory intensive. In addition, many reporting tools work much better with relational tables. Perhaps most importantly, a relational structure makes it easier to model the business and its processes, and simpler to amend as the business changes.

    Hopefully this helps, but to help further, we would need to know more about the data, where it comes from, what format it is in, and what you want to do with it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,197
    Location
    Yes the design is important and yours look decent.
    But Access is designed to do exactly that with custom tables, queries, forms and reports.
    Why to poster asks a database question on an Access forum and then suggests using Excel I am not quite sure.
    If he wants to use Excel it should be on the Excel forum.

  5. #5
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    5
    Location
    Many Thanks, SamT.

    It seems very helpful. I will have a look of it and may get a try.

    Br, Ji

    Quote Originally Posted by SamT View Post
    Google "dezign for databases"

    The help file that was therein used to be my GoTo for understanding databases

    I DLed Dezign 12.2 (14 Day free trial from the above search._ That help is not what I have

    What I have is in the attached zip, start with the "theory" chapter.

  6. #6
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    5
    Location
    Many thanks, Bob, for you detailed advice.

    Yes, the way to organize data is very useful. The products are customer-dedicated and -personalized, thus, the elements for every product are different with various models. The database needs to register all the types and barcodes.

    I will try Access and Power Query for the job.

    Br, Ji
    Last edited by Bob Phillips; 11-24-2020 at 12:42 PM. Reason: Removed redundant quote

  7. #7
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    5
    Location
    Hi OBP,

    Thanks for your comments. It is the first time I make a post here. It might not be set in a right place. If so, sorry.

    Br, ji

    Quote Originally Posted by OBP View Post
    Yes the design is important and yours look decent.
    But Access is designed to do exactly that with custom tables, queries, forms and reports.
    Why to poster asks a database question on an Access forum and then suggests using Excel I am not quite sure.
    If he wants to use Excel it should be on the Excel forum.

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,306
    Location
    the elements for every product are different with various models. The database needs to register all the types and barcodes.
    A Bar code makes a good Primary Key. I would have at least a Table for each Model

    Excel is a very good Data Base Designer helper tool. One sheet for each Table and Columns Headers for each Field,

    If you find that some Fields, (attributes) are common to ALL Model's Tables, they can be grouped into one table. If you find that some Fields are common to MANY tables, they can be grouped by "Family". All that means the you will need more (fast) Index Tables. Good Index Tables are critical.

    You will see that all our responses are extremely vague, because specific responses require specific knowledge of your Data.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,306
    Location
    @ OBP,

    Mea culpa. I moved the thread here just before I posted the first response.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    5
    Location
    Hi SamT,

    Thank you so much for the good strategic guidance. I'm going to make a few Excel sheets to register all the data based on the functionalities. Then, map them into relevant Tables and group the common attributes. I'm gradually getting clues to the right tracks. Thanks once again.

    Br, ji
    Quote Originally Posted by SamT View Post
    A Bar code makes a good Primary Key. I would have at least a Table for each Model

    Excel is a very good Data Base Designer helper tool. One sheet for each Table and Columns Headers for each Field,

    If you find that some Fields, (attributes) are common to ALL Model's Tables, they can be grouped into one table. If you find that some Fields are common to MANY tables, they can be grouped by "Family". All that means the you will need more (fast) Index Tables. Good Index Tables are critical.

    You will see that all our responses are extremely vague, because specific responses require specific knowledge of your Data.

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,197
    Location
    Sam, no problem, I just thought it was a bit odd.
    Excel is much easier to work with than Access and for small databases quick to produce.

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,306
    Location


    All we really know is that there are many machines providing much data.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •