Roadblocks Building Dashboard-- Programming Buttons to Apply Filters

[ForeverIntern]

Hi all! Forum noobie/lurker, so if this is the wrong sub, apologies in advance. Just point me in the right direction.



I'm currently implementing my first Access DB as an internship 'capstone' of sorts-- trying to accomplish the following for my company's Production Planning department:

--I want planners to be able to view their missed POs, edit them (adding a delinquency code, missing matl #, and notes) and upload them into a Root Cause table so that I can perform top-down analysis on what is causing missed orders in the plant.

1. SAC (SAP Analytics Cloud) raw data of missing/backlogged production orders is pulled and uploaded into a PP KPI Database. Completed

2. Data is filtered, and assigned to a value stream (and thus, a production planner) based off of production supervisors (a 3 character code, ie. V21, C02, A11). Completed.
2a. This data is a combination of a Backlogfile and a DR file. Because of this, data needed to be matched up based off a combination of sales orders & line items to eliminate repeat items.
2b. There is a possibility of a PO existing in both backlog and DR, so I used joint fields, SOLI_B and SOLI_DR, to identify them-- in other words, if two records have the same SOLI_B and SOLI_DR, they will combine into a single row of data. Others that don't have matching fields will either have an empty SOLI field, either B or DR. This does not matter.
2c. This was accomplished using SQL

[Daily DR].[Sales_doc] & '-' & [Daily DR].[Sales_item] AS SOLI_DR, [Daily Backlog].[Sales_doc] & '-' & [Daily Backlog].[Sales_item] AS SOLI_B

to concatenate the fields.

3. Those queries pulled and matched the data, as well as uploaded them into a new table, (ValueStream)_Workspace. This was only done because you cannot edit in queries that pull data from multiple tables. A production planner would run each PULL query, edit in the WORKSPACE, and use a final query, (ValueStream) UPLOAD, to upload their edited data into a Root_Cause table. Completed.

I have the first three steps complete, but do not want planners to have to run 3 queries manually as well as have the ability to work in other planners' sections on accident.

Here is where I ran into my biggest roadblock yet, and where I come to you for support:

I am trying to create a dashboard (form) to have planners enter in their Value Stream from a combo box, enter some optional data into different filter fields, and have multiple queries run at the click of a button. Unfortunately, I am a noobie at all things forms, reports, VBA, etc. I'll attach some documentation on what I want done, but am having a lot of issues getting any code I write to work.

Is there an easier way to accomplish what I'm trying to do? I'm sure there are about 60,000,000 facets within this DB that do not follow best practice. I'm learning on the fly.

Thanks in advance!

[ForeverIntern]

Also, I'm having issues attaching the actual Access DB. If anyone feels more comfortable actually looking at the DB itself, let me know and I will get it to you one way or another. Thanks again

[orange]

What issues with attaching db? Could just be that you recently joined and low number of posts.
Try attaching again. I think minimum 3 posts required.

[ForeverIntern]

I think minimum 3 posts required.

Added as .Zip file. Didn't realize 500kb file limit. Zipped it and uploaded fine.

Thanks for the assist!

[June7]

What purpose does DashboardSelectValueStream combobox serve? It is not used in filter criteria.

Parameters for text fields will need apostrophe delimiters, data fields need # and numbers nothing.

AP Form RecordSource does not have Production Supervisor so why would this be part of filter criteria?

Advise to eliminate spaces from naming convention.

Root Cause has text primary key field linked to autonumber field in Daily Backlog. This will not work in query. That text field must be Long Integer type.
Linking tables on primary key ID fields results in a 1-to-1 relationship. Is that what you want?
Also shows Daily DR autonumber linked to Daily Backlog autonumber. This cannot work. Cannot link autonumber fields.

[orange]

Glad to see June is looking at your database. I looked at your relationships and am concerned!
I suggest you describe the business entities and processes involved so readers have some understanding in order to assess your underlying structure.

[ForeverIntern]

Glad to see June is looking at your database. I looked at your relationships and am concerned!

Really appreciate the help thus far-- will get back to June in a bit. Probably should have specified earlier that the initial DB was not built by me, I'm picking up a "dead" project and trying to breathe new life into it.

Will reevaluate the relationships and give you both some context by EoD. Thanks so, so much!

[ForeverIntern]

What purpose does DashboardSelectValueStream combobox serve? It is not used in filter criteria.

My ideology was to have the combo box determine which form & queries would be ran based off of the static selections.

Parameters for text fields will need apostrophe delimiters, data fields need # and numbers nothing.

Advise to eliminate spaces from naming convention.

Will clean both up, ty.

AP Form RecordSource does not have Production Supervisor so why would this be part of filter criteria?

It should have a Prod_Sched iirc, probably used the wrong terminology. Each row of data will, when pulled from SAC, have an attached Date, ProdSched, Mat'l, Sales_Doc, Sales_Line, Description, and Order.

Root Cause has text primary key field linked to autonumber field in Daily Backlog. This will not work in query. That text field must be Long Integer type.
Linking tables on primary key ID fields results in a 1-to-1 relationship. Is that what you want?
Also shows Daily DR autonumber linked to Daily Backlog autonumber. This cannot work. Cannot link autonumber fields.

I should have mentioned, like I told Orange, that this is not originally my DB-- just a passdown. I didn't realize until now the relationships, field types, etc. were such a mess.

This is also my first non-classroom Access experience. Really, really appreciate the help from you guys-- I was hoping to not look totally ​clueless. Mission failed.

[June7]

That should have been: "date/time fields need #"

[orange]

ForeverIntern,

Describe as best you can, in simple, plain English -"What is the business this database is intended to support"?
What are the major things? How do they relate one to another? What exactly is required to be produced (reports) for whom? What is the input and if external, significance/timing/frequency...?

May be helpful if you could describe .."A day at my work" or "A typical cycle through this business"

Bottom line is readers need to understand WHAT you are trying to support before they can offer focused suggestions on HOW in Access.

I see this,

planners to be able to view their missed POs, edit them (adding a delinquency code, missing matl #, and notes) and upload them into a Root Cause table so that I can perform top-down analysis on what is causing missed orders in the plant.

but what is the process from the start.

In a few words--what is the purpose of this database?

Who creates the PO?
What exactly is a missed PO?
Who reviews the "top-down analysis"? Is it just you?
What is the impact or range of impacts associated with missed POs?
Is/does the top down analysis get reflected in a change in business processes?

[ForeverIntern]

Can do-- realizing this morning I left you guys a giant brain scramble. Happy Friday I guess?

Let's break it down like this:

What does the company do?

The business is majorly focused on manufacturing. We make pneumatic parts. Within this business we have four major categories of parts,value streams: Drives, Cylinders, Air Prep, and Cylinders. Each of these value streams are assigned a production planner. Four value streams, four production planners. We use SAP as our ERP system.

What does the order cycle look like?

We can skip a lot of the logistics of getting to this point, but the important steps are as follows:

1. A production order (PO) is written by a member of the production planning team for their specific value stream.
2. The production order is sent to the shop floor, where various teams assemble.
3. The orders are completed and set as 'completed' within SAP-- unless they cannot be. These orders will remain as 'incomplete' and usually become flagged as missed orders because they are past their estimated completion date. This creates a missed Production Order.

What do I do? Or, what are we doing?

I am a college senior (go Bearcats) who has been doing support ops within the Production Planning department since the tail end of the pandemic. The majority of my job is data-driven; a lot of my experience is more on the operations side of manufacturing, so I've been enjoying pushing my boundaries technically. I have always been into tech (Zoomer), but have been taking on more and more projects in tech roles to get better at it. I do a lot of low-code software building, so a lot of what I know from a DBA standpoint comes from experience there. I do that as a side gig and I think everybody should give it a shot because it's amazing .

Here, I do a lot of analysis for teams regarding missed revenue opportunities.

When it comes to this specific use case, I developing a better way for production planners to see all of their missed production orders and make changes to them, adding notes as to why this order was missed. They add in a delinquency code (del_code), missing material data, or notes to explain why this PO went past its ship date.

With all of this data together in one spot, we are able to perform root cause analysis on missed POs. If 400/500 missed POs have the same delinquency code, we have data on the biggest driver of missed revenue within the business. That is the step we are on right now-- getting all of that data in one spot-- and for production planners to share a workspace housing all of their data.

After this, actionable items, would depend on the data acquired from this project. If it's something within the shop floor we can control, we attack that and optimize. It's very LEAN oriented. However, we can't do anything until we have a means to collect this data.

Hopefully that gives a little more insight into what I'm trying to accomplish here. Thank you for your patience; I'm really trying here!

[June7]

A manufacturing/assembly database that tracks components is one of the most difficult to design. However, it sounds like to me that is not the focus of this db. If you just want to track order status, that should be fairly uncomplicated.

Maybe you would find the MS Task Management database template a useful guide. Look at an order as a task. https://support.microsoft.com/en-us/...b-b9fce69605f8

[ForeverIntern]

Appreciate this!

[orange]

Do you have (or could you have) milestones within your production process that could provide timely feedback on projects(tasks) that appear to be in difficulty? The thinking here is that your current method seems to identify missed completion date. If you broke projects into tasks (finer granularity), then you might identify a "troubled task" and take corrective action earlier in the production cycle.
Related reference/article- critical path.

[ForeverIntern]

Do you have (or could you have) milestones within your production process that could provide timely feedback on projects(tasks) that appear to be in difficulty?

There are methods in place that planners currently use when assigning daily orders to the shop floor. If they know a part cannot be produced, they won't release it for manufacturing.

I should have stated earlier this is a large-scale industrial setting. The hundreds of orders seen in this DB are a fraction of tens-of-thousands produced weekly.

If they are past their completed due date, for whatever unforeseen reason, they are updated daily into the DB. That is the most granularity we are able to work with at the moment without redesigning a multi-billion dollar corporation's manufacturing process.

I discussed this with someone over PMs, but as it stands, the DB does what I need it to do:

-All data on missed production orders is uploaded.
-Two queries can be used to pull data, filtering them by Prod_Sched to have them flow into the correct value stream workspace.
-Edits can be made within that workspace by planners.
-A query can be used to upload that workspace's data into a root cause table- I can then export that data (or keep it in Access) to perform analysis and make sweet, sweet Pareto charts

What I am trying to accomplish now:

-I am more or less just trying to poka-yoke this process using a form view so that planners don't have to run 3 individual queries and open a table. That is a recipe for someone to overwrite someone else's data if they aren't paying attention.



I greatly appreciate all the sound advice on database structure, manufacturing processes, etc., but for right now, I just want a dashboard with a navigable, simple UX that automates the process of running these queries-- maybe apply some filters. If we need to reevaluate how data is formatted to fit that, I have no issue with that. However, it is not within the scope of this project to recreate manufacturing processes or introduce new tech in order to better evaluate production order status.

Hoping this doesn't come off bitter or ungrateful-- you guys have been an incredible help so far. I just don't want to have this project fall apart due to scope creep.