Can this report be done in Access? (1 Viewer)

hbrehmer

Member
Local time
Today, 00:36
Joined
Jan 10, 2020
Messages
78
Hi All,

I have a report that I need to create based on data that is entered in my database. I need to total mold counts by day, and then total for the week and give an average mold count per day (as in the Excel example). But then I need it sorted in columns by 4 different customers (CUSTID) and then everyone else in "Reg". I have tried this report multiple times, but I keep getting multiple days listed in the week for each column, not one date in column 1. I really hate doing this report manually in Excel when all the data is live in my program.

Any ideas would be helpful.

Heidi
 

Attachments

  • Capture.JPG
    Capture.JPG
    93.4 KB · Views: 119

theDBguy

I’m here to help
Staff member
Local time
Today, 00:36
Joined
Oct 29, 2018
Messages
21,358
Hi. Looking at your report, my first impression is "yes, that should be possible in Access." But, it really depends on your data structure. Best way to get help is probably to post a sample copy of your db with test data. Cheers!
 

hbrehmer

Member
Local time
Today, 00:36
Joined
Jan 10, 2020
Messages
78
Here is a sample of the database. The original is quite complex at this point. But maybe you can see what is going on with my report.
 

Attachments

  • SHMTestDB.accdb
    600 KB · Views: 98

hbrehmer

Member
Local time
Today, 00:36
Joined
Jan 10, 2020
Messages
78
I really don't want to push. I know everyone on here is super busy with their own work. But can I get some eyes on my report. I need to present the finished database to my company at the end of June. I don't like to wait until the last minute with projects. I'm a nerd like that.

Thank you so much, everyone.
 

Dreamweaver

Well-known member
Local time
Today, 07:36
Joined
Nov 28, 2005
Messages
2,466
I've had a second look and to be honest can't see you getting there with that report and data set.

I don't know your project but:
Your doing loads of calculations I.E. =[SumOfTKMolds]-[GWCnt]-[PPCnt] Etc.
These should be done in the query if at all, I.E. =IIf([CUSTID]=36,[SumOfTKMolds],0)
You can build a query to list molds by customer or even a totals report.

I have a feeling you really need to look at normalising your data.
you have 76 customers yet you are doing this with 2 =IIf([CUSTID]=36,[SumOfTKMolds],0) and =IIf([CUSTID]=39,[SumOfTKMolds],0) this tells me there is something very wrong with your data.

I'm not really a reports guy so othersmay correct what I have said.

mick
 
Last edited:

hbrehmer

Member
Local time
Today, 00:36
Joined
Jan 10, 2020
Messages
78
I've had a second look and to be honest can't see you getting there with that report and data set.

I don't know your project but:
Your doing loads of calculations I.E. =[SumOfTKMolds]-[GWCnt]-[PPCnt] Etc.
These should be done in the query if at all, I.E. =IIf([CUSTID]=36,[SumOfTKMolds],0)
You can build a query to list molds by customer or even a totals report.

I have a feeling you really need to look at normalising your data.
you have 76 customers yet you are doing this with 2 =IIf([CUSTID]=36,[SumOfTKMolds],0) and =IIf([CUSTID]=39,[SumOfTKMolds],0) this tells me there is something very wrong with your data.

I'm not really a reports guy so othersmay correct what I have said.

mick
Thank you for your input. I know this looks so weird. But the company I work for does not do anything "normal". I'm having trouble convincing them to move from paper and pencil to manage production and sales. (The owners are in their 60s). Their moto is "That's the way we've always done it". Part records are still kept on index cards in an index file cabinet, SMH. I just wanted to give them a report that was automated to match the "stupid" report I keep in excel. It really is dumb, I know.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:36
Joined
May 7, 2009
Messages
19,169
you need to adjust the design of the report.
 

Attachments

  • SHMTestDB.zip
    110.3 KB · Views: 108

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:36
Joined
Feb 19, 2002
Messages
42,981
It would be good to normalize the tables. For example, Shippers should be in a separate table. Account numbers should also be in a separate table. Then the shipment method chosen for a particular order belongs with the order, NOT the customer.

Fixing the column and table names to remove embedded spaces and special characters will save you from annoyances as you get into queries and code.

The three tiny tables make no sense. You don't do yourself any favors by making object names so abbreviated that no one can figure out what they mean and using ID as the PK name in multiple tables is also not helpful.

And finally, you have no relationships established.
 

Users who are viewing this thread

Top Bottom