Access Report

Unless you have time and patience don't bother with this question.
I am a novice with Access so I will ask lots of questions and need lots of direction.  I have eliminated some data to reduce the file size and I think everything still works. Also I know my table are the best set up so any advice in that direction would be greatly appreciated also.
I have an excel sheet which I have put into Access so I can hopeful build some reports on.
I am trying to separate the data into different tables.
The data concerns Equipment and the routine maintenance for each piece of equipment.
My biggest obstacle is building a report which shows a count for how many times a certain PM occurs.
The unique identifier for the equipment is 'AssetName'
The routine maintenance is set up as follows:
Each unique task has a 'TaskNum'
These are grouped by 'PM_NUM'  -  This grouping identifies what tasks are in a grouping.
Additional grouping was them done and the main grouping is 'TaskCombo'
On my report 'rpt_TasksGrouping_Counts' I need to get a count of how many times 'TaskCombo' occurs by Trade and then get a total duration.

The report is now showing how many lines of task there are in each 'TaskCombo' by Trade and the total duration by 'TaskCombo'

Example: Taskcombo SAPPM00010  occurs only once, it has 4 lines of tasking, each line requires 0.0001 for duration and it is for the 'O' (Operator Trade).

I would like it to show the following:

SAPPM00010 occurs 1 time for Trade 'O' for a total duration of 0.0004

Also I will be AWK for 15 hours.
Strategy12_18.accdb
ssblueAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Can you rephrase your question please or at least clarify it? Do you want to work with that data model in your sample file or do you want to do a clean rework in Access?
Capture.PNG
ssblueAuthor Commented:
I would like to set it up correctly in Access so I can get the reports that I need.
ssblueAuthor Commented:
The table tbl_Tasks has all the fields from the excel table. I tried to separate them as best I could. I didn't want to eliminate any from the original until I was sure I wouldn't lose any data and I understood how to set up the relationships.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Fabrice LambertConsultingCommented:
Well, the data model is wrong to begin with.
Foreign keys should be the same type as primary keys, and you should apply referential integrity with your relations.

Also, choose meaningfull names for your tables.
You speak about  "Equipment", "routine maintenance" and "piece of Equipment", yet you have no table reflecting these concepts.

Finally, give up hungarian notation, it provide nothing usefull.
1) The days of computers with only text UI are over, today MS Access interface give us more than enough visual clues on what are tables, queries, modules, macro, forms ect ....
2) Last time I checked, nobody was calling you HomoSapiensSapiensFemale_ssblue.
ste5anSenior DeveloperCommented:
Then we need to begin with correcting the data model. Here it is useful to described the used entities and their relationships in plain, simple and correct language (and being correct is the hard part). E.g.

- A task uses one or more pieces of equipment.
- A task is executed in one primary laboratory.
- A task, when executed, has a start and end point in time.

Just complete this description by carefully looking at each column from your Excel sheet.

Then we can derive the entities: Tasks, Equipment, Laboratories.

Each entity is a separate table. In the given sample Laboratories consists only from the columns LaboratoryID, LaboratoryName. Then you use this as lookup for your primary laboratory column.

The equipment description, if being correct means, that you need an Equipment table (EquimentID, EquipmentName, ..) and a junction table between the Tasks table and the Equipment table to allow to assign multiple pieces to a task.

These description also tell you something about the cardinality between entities, thus the tables. Here it is important that you define primary keys and foreign key relationships accordingly. Then and only then Access as RDBMS can help you keep the data clean. Cause it enforces these constraints. So that the data stored fits your data model.

Caveat: While these things may sound being simple or easy, they are the real groundwork for a relational database. The quality of the reports will depend on it. So invest some time in this step.

Further comments in addition to Fabrice:
- Using prefixes in Access for the object type, like tbl, qry, frm makes not really sense. The UI helps us and we're wasting 4 characters in the case of long object names.
- Use always speaking names for your objects, tables, columns, forms etc. Only use abbreviations when the are commonly understood. E.g. if a first day intern does not know them, then don't use them. In the long term this will save you a lot of unnecessary brain power ;)
- Decide using one naming strategy. Either always separating words by using the underscore or never. This includes using upper-cased names also. E.g. SAP_STRATEGY or Sap_Strategy or SapStrategy. Abbreviations should only be uppercased, when having two characters.This increases readability.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I started looking at this yesterday, but didn't have time to go through it in detail.

As ste5an said, take all your facts (columns) and break them into what they belong to.  That will give you your tables.   And as he said, don't short change this step.  Ask a lot of questions about the design and make sure it's right before you go further.    Features in Access (like reports and queries) only work well if the database is designed well.

But on this:

Finally, give up hungarian notation, it provide nothing usefull.
1) The days of computers with only text UI are over, today MS Access interface give us more than enough visual clues on what are tables, queries, modules, macro, forms ect ....

- Using prefixes in Access for the object type, like tbl, qry, frm makes not really sense. The UI helps us and we're wasting 4 characters in the case of long object names.

 I have to disagree.  While the UI does help, it doesn't when your not working in the UI (aka in code).  There are many times with Access when it's beneficial to know the object type based on the prefix.  It's a good habit to get into and pays off down the road.   And three or four characters on an object name is trivial.

Jim.
Fabrice LambertConsultingCommented:
I have to disagree.  While the UI does help, it doesn't when your not working in the UI (aka in code).  There are many times with Access when it's beneficial to know the object type based on the prefix.  It's a good habit to get into and pays off down the road.   And three or four characters on an object name is trivial.
IMO variable names should not be coupled with variables types, their meaning is  more important to understand what is going on.

What if for whataver reason, some variables types or scope need to be updated ? (ops, we don't store byte anymore, but longs. Ops, the local constants are now global. Ops we now have a dictionary instead of a collection ect ......)
To stay consitent, you have to update all the related code, this is a non trivial, laborious and time-consuming task, that is better spent elsewhere.

Plus, functions and procedures should be on average 15 lines long, so seeing variables type is not an issue (most of the time, long functions break the SRP wich they shouldn't).
Narrow declaration is better, for lifetime reasons, wich again negate the need for hungarian notation as the variable type is right above its first use.
Even VBA's and Office libraries don't use it.

Hungarian notation was made at first for the purpose of giving meaning to variables names,. Sadly, it was diverted from its original goal.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
IMO variable names should not be coupled with variables types, their meaning is  more important to understand what is going on

 Well you were talking about object naming before, not variables.  But even so, it's quite handy to know what a variable is without having to go back and look at its declaration.   I think your in the minority here.

To stay consitent, you have to update all the related code laborious, this is a non trivial, and time-consuming task, that is better spent elsewhere.

 It is not....a find and replace takes mere seconds.

Jim.
Fabrice LambertConsultingCommented:
I think your in the minority here.
What's this ?
"The reason for the strongest is always the best" Jean de la Fontaine (1668) ?
I don't think so, history is full of samples.
ssblueAuthor Commented:
While I appreciate all the discussion, as I explained in the initial question, I am a novice here and I am in need of specific directions. Step 1, Step 2, etc...
I don't really understand some of your comments so please forgive me for my ignorance. I understand that Access is a very powerful program and capable of doing much more than I can even imagine. Having said that should you feel what I am asking is beyond my capabilities within a narrow window of time please let me know. I will withdraw the question and continue to simply use Excel.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry for getting off on a tangent.

Having said that should you feel what I am asking is beyond my capabilities

 I'm sure it's not, but it does take time to do it right.

within a narrow window of time

 Define narrow.  If a few days, then that's probably not going to happen.  Few weeks is more in line.  

 That's not solid work on your part mind you, but you asking questions, getting answers, understanding, and doing the work.  As you say, this will be a learning experience for you, and that will take time.

 A lot of it is just common sense, but the concepts (relational design) can seem strange when your first exposed to them.  

Jim.
ssblueAuthor Commented:
So where do I start?  Should I split this question up into multiple questions?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.