I'm trying to update an Access database to be easier to maintain and update. To anonymize the data, I altered the data to use food ordering as a metaphor/analog. My experience with Access is not exactly basic, but very much learning to solve issues as I've come across them. So I don't know how close, or how far off, I am currently.
A Google Sheet I'll be referencing: https://docs.google.com/spreadsheets/d/1UYt1SKbBrKqE6IWCTyUHVNV89jGfy4KoHWTzCXtO0CM/edit?usp=sharing
NOTE: In response to GMP's comment I tried to convert the information into this post directly.
Goal
How it started was each "order" was a single row in a table. There are 20 some near identical reports with minor differences that hard coded values based on the "product". The primary goal is to remove the hard coded per report data. In the end combining the "generic" and "order specific" certificate of analysis like data into a report. I've been trying to make this a subreport that I can drop into the 20 odd some reports until I can figure out how to prune that mess.
The goal, though open to better solutions, is sketched out in "Example Report".
https://i.imgur.com/t2tjPSb.png (Not enough karma to be allowed to embed an image yet, table-fied version below)
| Category Grouping: Spec_Category | |||
| Spec | Order Specific Value | General Analysis Value | |
| [Expr1001] and [Spec_Specification] | [Query_Order_Specific]![Order_AnalysisValue] | [Query_Generic_Analysis]![Spec_Analysis] | |
| [Expr1001] and [Spec_Specification] | [Query_Order_Specific]![Order_AnalysisValue] | [Query_Generic_Analysis]![Spec_Analysis] | |
| Category Grouping: Spec_Category | |||
| Spec | Order Specific Value | General Analysis Value | |
| [Expr1001] and [Spec_Specification] | [Query_Order_Specific]![Order_AnalysisValue] | [Query_Generic_Analysis]![Spec_Analysis] | |
| [Expr1001] and [Spec_Specification] | [Query_Order_Specific]![Order_AnalysisValue] | [Query_Generic_Analysis]![Spec_Analysis] |
What is done so far
General Data
I have the subreport working for the most recent general COA information. This is "Certificate of Analysis" data that is supposed to be the most recent value per Spec and Product pairing.
The format of this data is the "Query_Generic_Analysis" tab in the Google Sheets. It is working fine by itself. These would be columns A and D of the "Example Report".
| Spec_ProdType | Spec_Category | Spec_Specification | Spec_Analysis |
|---|---|---|---|
| Fries | Condiment | Mustard | 0.1045 |
| Fries | Condiment | Pepper | 0.0397 |
| Fries | Condiment | Citrus | 0.1886 |
| Fries | Condiment | Tears | 0.5445 |
| Fries | Condiment | Ketchup | 0.0835 |
| Fries | Physical Description | Red Color | 1.5 Max |
| Fries | Physical Description | Flavor | Bland |
| Fries | Texture | Free Fatty Acid (FFA) Oleic | 0.05% Max |
| Fries | Texture | Salt | 95 mg/l |
| Fries | Texture | Moisture | 0.10% Max |
| Fries | Texture | Vegemite | 42 ML/KG |
| Milkshake | Condiment | Mustard | 0.1045 |
| Milkshake | Condiment | Pepper | 0.0397 |
| Milkshake | Condiment | Citrus | 0.1886 |
| Milkshake | Condiment | Tears | 0.5445 |
| Milkshake | Condiment | Ketchup | 0.0835 |
| Milkshake | Physical Description | Red Color | 6.0 Max |
| Milkshake | Physical Description | Flavor | Bland |
| Milkshake | Texture | Free Fatty Acid (FFA) Oleic | 0.10% Max |
| Milkshake | Texture | Salt | 95 mg/l |
| Milkshake | Texture | Moisture | 0.10% Max |
| Milkshake | Texture | Vegemite | 42 ML/KG |
| Nuggets | Condiment | Mustard | 0.1045 |
| Nuggets | Condiment | Citrus | 0.1886 |
| Nuggets | Condiment | Tears | 0.5445 |
| Nuggets | Condiment | Ketchup | 0.0835 |
| Nuggets | Physical Description | Red Color | 1.5 Max |
| Nuggets | Physical Description | Flavor | Bland |
| Nuggets | Texture | Free Fatty Acid (FFA) Oleic | 0.05% Max |
| Nuggets | Texture | Salt | 95 mg/l |
| Nuggets | Texture | Moisture | 0.10% Max |
| Nuggets | Texture | Vegemite | 42 ML/KG |
| Salad | Condiment | Mustard | 0.076 |
| Salad | Condiment | Pepper | 0.033 |
| Salad | Condiment | Citrus | 0.755 |
| Salad | Condiment | Tears | 0.104 |
| Salad | Condiment | Ketchup | 0.022 |
| Salad | Physical Description | Red Color | 1.5 Max |
| Salad | Physical Description | Flavor | Bland |
| Salad | Texture | Salt | 10 mg/l |
| Salad | Texture | Vegemite | 42 ML/KG |
| Tofu | Condiment | Mustard | 0.076 |
| Tofu | Condiment | Pepper | 0.033 |
| Tofu | Condiment | Citrus | 0.755 |
| Tofu | Condiment | Tears | 0.104 |
| Tofu | Condiment | Ketchup | 0.022 |
| Tofu | Physical Description | Red Color | 1.5 Max |
| Tofu | Physical Description | Flavor | Bland |
| Tofu | Texture | Free Fatty Acid (FFA) Oleic | 0.05% Max |
| Tofu | Texture | Salt | 10 mg/l |
| Tofu | Texture | Vegemite | 42 ML/KG |
| Watermelon | Condiment | Citrus | 0.1886 |
| Watermelon | Condiment | Ketchup | 0.0835 |
| Watermelon | Condiment | Mustard | 0.1045 |
| Watermelon | Condiment | Pepper | 0.0397 |
| Watermelon | Condiment | Tears | 0.5445 |
| Watermelon | Physical Description | Red Color | 1.5 Max |
| Watermelon | Physical Description | Flavor | Bland |
| Watermelon | Texture | Free Fatty Acid (FFA) Oleic | 0.05% Max |
| Watermelon | Texture | Moisture | 0.10% Max |
| Watermelon | Texture | Non-GMO attributes | 99.1% or better |
| Watermelon | Texture | Salt | 95 mg/l |
| Watermelon | Texture | Vegemite | 42 ML/KG |
Order Specific Data
Using a Union Query, I've been able to get the order specific COA information. "Query_Order_Specific" is an example of how the query results currently look.
| tabKey | Order_Specification | Order_AnalysisValue | Order_Product | Order_Category |
|---|---|---|---|---|
| 8986 | Mustard | 2.8 | Watermelon | Condiment |
| 8977 | Mustard | 2.8 | Watermelon | Condiment |
| 8960 | Mustard | 2.8 | Watermelon | Condiment |
| 8952 | Mustard | 2.8 | Watermelon | Condiment |
| 8914 | Mustard | 2.8 | Watermelon | Condiment |
| 8911 | Mustard | 2.79 | Watermelon | Condiment |
| 8562 | Mustard | 3.14 | Watermelon | Condiment |
| 8560 | Mustard | 3.14 | Watermelon | Condiment |
| 8526 | Mustard | 3.14 | Watermelon | Condiment |
| 643 | Ketchup | 0 | Watermelon | Condiment |
| 642 | Ketchup | 0 | Watermelon | Condiment |
| 611 | Ketchup | 0 | Watermelon | Condiment |
| 135 | Ketchup | 0 | Watermelon | Condiment |
| 105 | Ketchup | 0 | Watermelon | Condiment |
| 84 | Ketchup | 0 | Watermelon | Condiment |
| 83 | Ketchup | 0 | Watermelon | Condiment |
| 642 | Salt | 0.03 | Watermelon | Texture |
| 8627 | Salt | 0.06 | Milkshake | Texture |
| 8625 | Salt | 0.03 | Milkshake | Texture |
| 524 | Ketchup | 0.07 | Nuggets | Condiment |
| 498 | Ketchup | 0.07 | Nuggets | Condiment |
| 491 | Ketchup | 0.07 | Nuggets | Condiment |
| 474 | Ketchup | 0.07 | Nuggets | Condiment |
| 463 | Ketchup | 0.07 | Nuggets | Condiment |
| 459 | Ketchup | 0.07 | Nuggets | Condiment |
| 458 | Ketchup | 0.07 | Nuggets | Condiment |
| 8626 | Salt | 0 | Fries | Texture |
| 8624 | Salt | 0 | Fries | Texture |
The Current Roadblock
The roadblock I'm currently having trouble figuring out is combining these two query sets. I can, and have, created two independent sub reports for each data set. So far, my attempts at joining the two queries in a larger query seem to only give one of two results:
- The Greater Query is entirely empty
- The Greater Query only returns results where both the Generic and Order_Specific analysis have values for the specification.
Looking at the "Example Report - With Pseudo Data", there are some specs that have generic data without order specific fata.
https://i.imgur.com/MYvoVgN.png I don't have enough karma to be allowed to embed an image yet, so a crude table conversion below
| Table Key: | 642 | ||
| Product: | Watermelon | ||
| Condiment | |||
| Spec | Order Specific Value | General Analysis Value | |
| Citrus | 0.1886 | ||
| Ketchup | 0 | 0.0835 | |
| Mustard | 0.1045 | ||
| Pepper | 0.0397 | ||
| Tears | 0.5445 | ||
| Physical Description | |||
| Spec | Order Specific Value | General Analysis Value | |
| Red Color | 1.5 Max | ||
| Flavor | Bland | ||
| Texture | |||
| Spec | Order Specific Value | General Analysis Value | |
| Free Fatty Acid (FFA) Oleic | 0.05% Max | ||
| Moisture | 0.10% Max | ||
| Non-GMO attributes | 99.1% or better | ||
| Salt | 0.03 | 95 mg/l | |
| Vegemite | 42 ML/KG | ||
EDITS
- Attempted to include the data into the post itself.
from Recent Questions - Stack Overflow https://ift.tt/34RU58k
https://ift.tt/eA8V8J
No comments:
Post a Comment