Report pulling and summing from multiple tables

[Deleted member 147267]

When I saw someone had already downloaded your file(s) I figured someone had a head start on me but also there's what I alluded to. Not trying to impress anyone, but here's my complaint list for perspective:
- oldest brother recently admitted to long term care. He has Lewy Body Dementia.
- his wife's long battle with cancer landed her in palliative care yesterday and he doesn't even know. Might not even remember when they tell him.
- my younger brother's oral cancer from last year invaded his lungs & he's on immunotherapy. This is a one week old development.
- I have a small table project (woodworking) that needs to be finished for Christmas
- I've been tending to my wife, who had spinal surgery 2 weeks ago
- my 7 year old washer broke down 2 days ago. I'm trying to shop for a new one. There goes $1,000. Given everyone else's issues, the washer doesn't seem so bad.


Issues that appear to be related to db design problems seem to require more focus than I'm able to provide at present. My advice would be to not assume too much about anything that goes on (or doesn't) around here. Lack of response could be as innocent as an accidental email notification deletion. Then it's out of sight and out of mind. You can always PM somebody if they have not disabled that. Hope some of that helps.

 

[lAMRITH]

When I saw someone had already downloaded your file(s) I figured someone had a head start on me but also there's what I alluded to. Not trying to impress anyone, but here's my complaint list for perspective:
- oldest brother recently admitted to long term care. He has Lewy Body Dementia.
- his wife's long battle with cancer landed her in palliative care yesterday and he doesn't even know. Might not even remember when they tell him.
- my younger brother's oral cancer from last year invaded his lungs & he's on immunotherapy. This is a one week old development.
- I have a small table project (woodworking) that needs to be finished for Christmas
- I've been tending to my wife, who had spinal surgery 2 weeks ago
- my 7 year old washer broke down 2 days ago. I'm trying to shop for a new one. There goes $1,000. Given everyone else's issues, the washer doesn't seem so bad.


Issues that appear to be related to db design problems seem to require more focus than I'm able to provide at present. My advice would be to not assume too much about anything that goes on (or doesn't) around here. Lack of response could be as innocent as an accidental email notification deletion. Then it's out of sight and out of mind. You can always PM somebody if they have not disabled that. Hope some of that helps.

No worries at all, I never expected or required an answer. I know my responses on forums at times can come across as dismissive and just wanted to apologize for that and let you know I did appreciate your response and the links, I know they will help.

Family is much more important that anything else, your focus is in exactly the correct place.

 

[mike60smart]

Hi

From the list below - are all of these for the 1 Customer?

AA Asphalt
AA Asphalt - stick
aa asphalt-bucket
AA Asphalting
AA Asphalting (Crowd Cyl)
AA Asphalting (Hi-Temp Cyl 1 of 2)
AA Asphalting(Hi-Temp Cyl 2 of 2)
AA Asphalt-Stick Cyl

 

[mike60smart]

Hi

I was also not able to Enforce Referential Integrity between the Jobs and Hours tables.

It informed me that there were records in the Hours Table with NO matching record in the Jobs table.

When I ran an Unmatched Query it listed the following records as Not being in the Jobs Table:-

 

[Gasman]

@IAMRITH,

I also noticed that the Hours table has 'cylshop' in place of a valid Job# ?

Mike's pic also shows 'certified' in that field?

 

[Deleted member 147267]

I know my responses on forums at times can come across as dismissive and just wanted to apologize for that

No worries. I did not take your response in that way and certainly didn't mean to suggest anything other than that you not read anything into a lack of response. We're all volunteers here, and if anyone steps out of line, somebody will let them know.

EDIT - my washer issue is no longer. Decided to replace the dryer as it's just as old, plus I figure I could probably get $100 for it. Bought a new microwave too. $2,000 out of pocket now. Learned a lesson also - don't bring Momma when you go appliance shopping!

 

[lAMRITH]

Hi

From the list below - are all of these for the 1 Customer?

AA Asphalt
AA Asphalt - stick
aa asphalt-bucket
AA Asphalting
AA Asphalting (Crowd Cyl)
AA Asphalting (Hi-Temp Cyl 1 of 2)
AA Asphalting(Hi-Temp Cyl 2 of 2)
AA Asphalt-Stick Cyl

@IAMRITH,

I also noticed that the Hours table has 'cylshop' in place of a valid Job# ?

Mike's pic also shows 'certified' in that field?

Yes and this exactly why I think normalization may be the best way to go. Sheer user laziness/inconsistency causing errors. I have read thru the normalization pages linked earlier and it def makes more sense to me. I had avoided it as I though it was un-needed complexity. I partially normalized, but did not take it all the way thru.

Yes those "AA" are all the same customer, but they also put a partial item description in that field as well. cylshop should not be in that field either and neither should "certified" (our parent company). The jobs starting with 5 should have been as those are in the cylinder shop numbering sequence but very old. Could be a mistype as we are up in the 52xxx realm. I will have to go in and manual correct those entries after doing some research.

The company just upgraded our Acct system (SAGE50) and they plan to expand and take advantage of the job ticket/workorder functionality as well as inventory (Yes we currently use :banghead: Excel for our inventory tracking.) SO our needs for this dB may drastically change after Jan 1.

At this time I think I am going to call this request closed/shelved. It is very likely I will post a new thread early next year in another section to revamp the dB.

I want to thank the group of you for taking the time to look into this and give me ideas. It is greatly appreciated and I will be in touch in the future.