Small Database PIM & MDM, help needed!

[JJKJJK]

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

[SamT]

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.

[Bob Phillips]

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.

[OBP]

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.

[JJKJJK]

Many Thanks, SamT.

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

Br, Ji


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.

[JJKJJK]

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

[JJKJJK]

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


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.

[SamT]

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.

[SamT]

@ OBP,

Mea culpa. I moved the thread here just before I posted the first response.

[JJKJJK]

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

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.

[OBP]

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.

[SamT]

:cloud9:

All we really know is that there are many machines providing much data.