Report pulling and summing from multiple tables (1 Viewer)

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
Total Novice Access user. While I have used access for years I have only ever needed to use the basic built in features for organizing and tracking information and making basic reports. I am a purchasing/CS guy and have zero training or experience in programming/SQL/Macros, etc.

I work for a repair shop that takes in customer units, tears them down, then quotes to repair as needed.

The current dB has 2 tables (linked via relationship on job# field)
  • JOBS-(One entry per job #)-All basic job information, numerous dates, notes, cost, hours estimations, etc.
  • HOURS-(Many entries per job#)-job#, date for work, techinician, hrs per task

The management now wants a report to show yes/no of jobs by customer so we can see the ratio of how many they approve versus decline.
I have created a report (and supporting crosstab query) that will tell us how many "Go" and how many "No-job" for each customer. The querry only has the Job table in it.
Now I need to be able to add up the $ for only the "No-job" jobs and show it so they can see the lost revenue for just those. They also want to see how much total labor the shop has put into the "No-jobs" for that customer. I have tried a number of times but I just cannot get it to work. I also find if I add the hours table to the crosstab query, the go/nogo job count stops working.
Can anyone help? What do I need to maybe upload so you can see what I have so far?
 

Micron

AWF VIP
Local time
Today, 17:15
Joined
Oct 20, 2018
Messages
3,476
Maybe an upload would be a good idea. With few posts, I think the forum requires you to zip your file first.
You might be over complicating this as I'm not seeing a need for a crosstab query, and unless it always has the same number of fields (or you have taken design precautions) reports based on it will fail the first time the number of crosstab fields is different than what you designed the report for.

Always start your report with a query that returns the records you need. Report sorting, grouping and summing are usually best left to the report. In this case, all the no jobs for each customer would be a grouping, with totals in the group footer.

EDIT - I'm surprised that you only have 2 tables for this. Doesn't sound right.
 

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
ok, I will review what I need to do to upload when I get to work tomorrow.

Interesting point on the query/report aspect. I said I am a complete newby. I could not get the job count data to output like I wanted so I started goofing around and when I made a crosstab it suddenly worked so I stayed on that path.

The data is pretty simple and straight forward which is why I only have 2 tables. They did it all on a single Excel tab before, it is pretty much just a log of all the jobs. I do not do any inventory tracking/transactions or anything like that, it is just to track job status so mostly is different dates of when certain aspects of the job are due, estimated hours by task, quoted $, etc. Then the hours table is basically just an hours log book as well.

Thanks for the input, I will try to get something uploaded tomorrow!
 

Micron

AWF VIP
Local time
Today, 17:15
Joined
Oct 20, 2018
Messages
3,476
I get the jobs and hours tables, but no tblTechs or tblCustomers at the very least? Maybe tblJobTask and tblTaskNotes and tblJobNotes as well? When I see comments that make comparisons to Excel, it raises red flags. Spreadsheets and databases are different animals altogether and one shouldn't be patterned after the other.

If it helps, I spent a lot of time in maintenance reliability - some of it after I quit being a mechanic so I have some work order/PM/reliability and mechanical experience that I might be able to bring to this.
 

isladogs

MVP / VIP
Local time
Today, 21:15
Joined
Jan 14, 2017
Messages
18,186
Create a query joining the two tables by JobNumber.
Filter the Yes/No field for 'NoGo' only.

Now convert to an aggregate query.
Group by JobNumber and Sum the Hours field. Add any other fields needed
 

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
I get the jobs and hours tables, but no tblTechs or tblCustomers at the very least? Maybe tblJobTask and tblTaskNotes and tblJobNotes as well? When I see comments that make comparisons to Excel, it raises red flags. Spreadsheets and databases are different animals altogether and one shouldn't be patterned after the other.

If it helps, I spent a lot of time in maintenance reliability - some of it after I quit being a mechanic so I have some work order/PM/reliability and mechanical experience that I might be able to bring to this.

This is not our accounting system We have an entirely separate accounting/inventory system. This is just to aid in job tracking/visibility. The company is very old school and everything is still and will stay primarily on a hard work order/job packet format. 2yrs ago it was just an excel file with job number, co name, in date, due date and dollar amount. Hours per job were manually calculated and summed by job, using Excel along with variance, etc.

I totally agree with spreadsheet and database being different. SOOO many people use Excel as a Database. It works for very simple ones, but is just not the right way to do things, plus limited to one user at a time. We have 2-3 people in our dB at all times.

I started from ground up with the data they wanted to capture and then started developing the table format knowing they would want to expand. I have a tech table, but it is literally one field "tech name". We do not keep any other information in it, nor do they want any more info in it, so I have not bothered using it beyond there being a table. I should note, I have split the dB into one dB with the tables in it, and another with all the forms/queries/reports. I then have a second dB that is copy of the forms/reports one that I use for development so I do not have to kick them out to make new reports or work on fixes/features like this new report.

Our Techs are not paid from logged hours and customers are not billed from it, so I did not want to make it any more complex than it had to be as I was/am already at my max ability. I did not see any need to add complexity as I would not gain anything from it. I also worry about making the dB less stable for the users, one is not computer savy to the point they constantly make errors that cause errors on reports in the dB and even manage to have their printer stop working frequently by doing something. I had to move them to an Access Runtime to maintain my sanity as they kept messing things up.

I will try to get the dB up tomorrow if possible.
 

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
ok, crazy busy morning, finally was able to merge the dB into one file .mdb and zip it.

Please be kind, we are a very small company and I am a novice learning as I go, just trying to get them off of Excel sheets and into a more robust and flexible system so it is messy and not very advanced.

This report is for cylinder shop, but all jobs for both Departments are in one primary job table. I was going to split it, but moving forward in a year or so the Company will be bringing all work orders into a single numbering program so I kept them all together and have a field for department.

What I have working so far:
QUERY: CylJobsSummaryReportQuery
REPORT: CylJobsSummaryReport

Key fields for this report/query are:
  • customer - obvious use
  • indate - entry used on query start to filter time the report covers based on recieved date.
  • godate - triggers when it should count for go jobs, need total Gojobs for customer in date range
  • nojob" triggers when it should count nojobs, need total nojobs for customer in date range.
  • quoteprice - price quoted for the job, need to sum all nojobs up to show total lost revenue on report.
  • HOURS TABLE - Need to total the hours charged to the job numbers that are nojobs for each customer.

I attach a pdf copy of the current report I am trying to duplicate as well. They are calculating that sheet manually (shudder) at this time.
 

Attachments

  • Cylinder Go-No by customer.pdf
    77.9 KB · Views: 113
  • Labor - EDITING.zip
    1.3 MB · Views: 113

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
I think a lot of this difficulty is because your structure is wrong.
You have built the Hours tables as an Excel worksheet.

I assume the numbers in the columns are hours for particular tasks for a job?, stripdown, hone, polish etc.?

A the very least the tasks for the Job should be in separate records. then it would be very easy to total.?

When I try and run your report, it not only asks for the start and end date, but the Sum fields as well.?
 

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
I think a lot of this difficulty is because your structure is wrong.
You have built the Hours tables as an Excel worksheet.

I assume the numbers in the columns are hours for particular tasks for a job?, stripdown, hone, polish etc.?

A the very least the tasks for the Job should be in separate records. then it would be very easy to total.?

When I try and run your report, it not only asks for the start and end date, but the Sum fields as well.?
Thanks for the response. Sorry I am so new and clueless with what you are recommending.

Not sure I follow on the tasks per job being separate tables? I am open to making changes, especially if it makes my future report generation easier. Just not sure I follow what you are recommending.

In the jobs table the "estxxx" are our target hours for that task for the entire job. Then in the hours table it is the actual physical hours the tech put into that task by job, tech and date. What are you recommending is in a separate table for each task?

The hours table itself is built like the turn in sheets I get from the technicians(image attached). They all do multiple tasks often for the same job in the same day. Are you saying I need to have a table for each task and then enter each techs time separately in each table? That will double to quadruple data entry time having to enter the same information with job number and tech and date on every table? Or am I probably missing something?

Yes the summing request was me trying to make formulas to give me the totals I want and failing miserably, so the query was asking me for the information since it did not exist.
 

Attachments

  • 20191211_101254.gif
    20191211_101254.gif
    98.7 KB · Views: 99

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
How do you define nogo?, those with a date in NoGo?, as a lot do not have a quote price?

I am not saying different tables but the hours table would look like

HoursID
JobID
EmployeeID (Technician)
TaskTypeID (possibly link to another table that holds Task descriptions
EstHours
ActualHours

and anything else that you need like Nogo date.

Then it would be simple to group by customer and sum worked and Nogo tasks.?

The keyis being able to identify what type of task it is, whether it is a go or no and anything else you need.

I would have thought that you could have a few tasks processed before it is judged NoGo?
Admittedly I am not the best to suggest how the structure should look.
Hopefully one of the experts will chip in.?

It is wasier to sum fields in different records, rather than sum fields in teh same record.
What happens if another task is introduced?, you will have to change all the code.?

HTH
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
Just had a look at what you have made.
I must say very impressive for the novice that you say you are.

Plus all the work you had to do on that report with all the IIF() because of the incorrect structure, but very imoressive just the same.:cool:

I realise you have gone a long way into this and changing now would seem a lot of work, but I believe it would benefit you now and in the future.

However I would like one or two experts to chip in with their views/opinions?
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
BTW the DB should be split and the users should only have a accde to run. That way it should stop any playing around.

However if a control needs to be completed for a record to be saved, or if one control is completed, then another must also be completed, that is all down to you and anyone else who would code this DB. Hard I know, if you are a novice. Been there, done that. Fortunately no need to now. :D
 

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
BTW the DB should be split and the users should only have a accde to run. That way it should stop any playing around.

However if a control needs to be completed for a record to be saved, or if one control is completed, then another must also be completed, that is all down to you and anyone else who would code this DB. Hard I know, if you are a novice. Been there, done that. Fortunately no need to now. :D
This dB is normally split in two. Tables in one dB and all the other items in the other. I compressed back into one file thinking it would be easier for people here to help me.

I split it into two so that I could edit forms/reports anytime I wanted to and not affect users. Plus yes they cannot tinker with things.

Everyone but me runs Access runtime as well for same reasons, plus free. no way they were willing to pay for Access for everyone.

I can upload the two independent files if that is preferable.

I appreciate you helping me so late in your evening as well, Thank you.

If scribbling a drawing and emailing to me for structure helps, go for it, I am open to anything that will help make this growing monster better and understand it can be tough to communicate electronically like this.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
No that is the way to do it, again well done.:cool:

Could you use your cylShopGo and Nogo queries to get what you want?
You might have to use a UNION query to get your data from each, then group by customer?.

Have that UNION query as your base, then work from that.?
You could assign a new field as n or g to identify which is which, then do you math with you IIF()

In fact you could probably just do that in a new base query, then group and sum from that, no need for a UNION at all.?

I'm just trying to think of workarounds, now I see how far you have got into this. Not good practice perhaps, but you have what you have.

Amending now would be a fair bit of work.

Would I be correct in guessing there wil be more depts other than 'cyl'? So perhaps getting it better structured might still be the way to go.?

Micron said he had a fair bit of experience in this field.?

This dB is normally split in two. Tables in one dB and all the other items in the other. I compressed back into one file thinking it would be easier for people here to help me.

I split it into two so that I could edit forms/reports anytime I wanted to and not affect users. Plus yes they cannot tinker with things.

Everyone but me runs Access runtime as well for same reasons, plus free. no way they were willing to pay for Access for everyone.

I can upload the two independent files if that is preferable.

I appreciate you helping me so late in your evening as well, Thank you.

If scribbling a drawing and emailing to me for structure helps, go for it, I am open to anything that will help make this growing monster better and understand it can be tough to communicate electronically like this.
 

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
Yeah Def pretty far into the rabbit hole.. However if Amending is what needs to happen to make the dB more robust and usable long term, it is what needs to happen. Rather do it now than after another year worth of data has to be converted. I just do not know or understand how to break it up or how it will help.



Interesting idea on using the other queries, I had never considered that, I will do some searches on Union queries.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
My approach without amending the structure (for now at least) would be to create a new query for the data you need for this new report.
You can create new fields in the query on the fly.
I am thinking that the report could look like a bank statement with go values in one colum and nogo values in another. Same with count of each?
Create a new field to identify whether record is go or nogo for the report and use IIF() as you have done in your other reports, but in the query or report, which ever you choose.

Then just sum and group by customer? and perhaps a report total for everything.

Only you know what you need on this report at present.

This could just be one query if you do it this way.

As this is 'live' data, you will need to be careful if you restructure it obviously. That in itself is a fair bit of work, making sure that data is all correct when restructured, but you are so far in now, I cannot see an easy alternative.

I would even consider how it is likely to change in the future, and is all the extra work to do that really worth it.? Only you can answer this.?
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
Yeah Def pretty far into the rabbit hole.. However if Amending is what needs to happen to make the dB more robust and usable long term, it is what needs to happen. Rather do it now than after another year worth of data has to be converted. I just do not know or understand how to break it up or how it will help.



Interesting idea on using the other queries, I had never considered that, I will do some searches on Union queries.

I do not think you even need a UNION query. Just copy one of the go or no go queries (to save you dragging a few fields) or start from scratch and try what I suggested in my last post.
 

Micron

AWF VIP
Local time
Today, 17:15
Joined
Oct 20, 2018
Messages
3,476
Maybe you could use a review on normalization. I've restrained from posting everything that I usually do because your post doesn't really indicate a need. Unfortunately with everything that's going on around here I haven't been able to devote a lot of time to complex issues (and things are just getting worse).

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html

and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.com/2009/05/how-do-i-create-application-in.html
 

lAMRITH

Registered User.
Local time
Today, 14:15
Joined
Dec 4, 2019
Messages
17
Maybe you could use a review on normalization. I've restrained from posting everything that I usually do because your post doesn't really indicate a need. Unfortunately with everything that's going on around here I haven't been able to devote a lot of time to complex issues (and things are just getting worse).

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html

and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV http://rogersaccessblog.blogspot.com/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access? http://rogersaccessblog.blogspot.com/2009/05/how-do-i-create-application-in.html
Thanks for your reply. I was worried that my reply after your initial post had caused you to no longer respond. I was just trying to give everyone a full picture of our situation. Reading it a few days ago I realized it read a bit like I was shutting you down and unwilling to listen to your input, sorry about that, it was not my intention.

I am/was hoping to not have to so a total revamp of the database, but based on what I am hearing it may very well be the best thing to do. If that is the case, now would be the time before I get another year of data to deal with.

I will check out those links and probably be back with a huge list of questions.
 

Users who are viewing this thread

Top Bottom