Creating a Form and Report from Multiple tables (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 10:09
Joined
Jul 10, 2019
Messages
277
Hello Guy's

I have another challenge to accomplish and could use some help.

I attached an excel document that mimics the form and report I need to create. (I made it pretty clear where the data was coming from).

The data is coming from a few different places.

Points table, Non_Standard_Actions table, Query1 for counting the standard actions.

I made three queries that get the data I need: PointsQuery, NSATotByWeek Or NSACrosstabTotByWeek, and CountAllSAByWeek.

Now I have no idea how to combine all the data into a form or report. I uploaded the database to google drive, it's too big to upload here. The UserID that I have been using for testing is UserID=2 (Holly Flowers). That's the only one that has all the points for all 12 weeks. All data is fictitious for testing purposes. Here's the link


I tried to make my needs pretty clear, but please ask as many questions as you need.



Thanks
 

Attachments

  • PointsByWeek.xlsx
    10.5 KB · Views: 128

theDBguy

I’m here to help
Staff member
Local time
Today, 10:09
Joined
Oct 29, 2018
Messages
21,358
Hi. I don't see any problems. Unless, are you trying to create a data input/edit form?
 

Lkwdmntr

Registered User.
Local time
Today, 10:09
Joined
Jul 10, 2019
Messages
277
I made the excel sheet manually, with no code or queries. I need to replicate that in a form for viewing and also as a report for printing in Access, not Excel. I don't need to edit it, just view and print. I would like a form version as I have with the reporting forms.

Not sure how to grab all the data from all the different queries and combine it all into one form and report.

I will be adding a button to the "History" section and after selecting a client, the form version will pop up with an option to print, which will pull up the Report version to print. At least, that's the plan. I would think that the Form and Report would basically be using the same query that brings all that information together. I hope that makes more sense now.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
I've taken a quick look at your db and it looks like you are applying an Excel logic to your thinking.

1. I haven't checked all of them but you have an awful lot of multiple queries, one for each day of the week where the only difference appears to be the name of the weekday.

2. you have a number of tables where you either have repeating columns (again for each day of the week) or horizontal 'lists' of values which I suspect could be extended over time so should be listed in a vertical table.

So I think you do need to look at your design because with the above two points you make it harder to do anything more complex - or at at best, extremely long winded.

Best I can suggest at the moment for this new form is to create a new query or queries based on the tables, not based on the individual queries.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:09
Joined
Oct 29, 2018
Messages
21,358
I, on the other hand, haven't looked at your database. So, if you haven't designed it as a database, then I agree that you should stop now and do that first. But with regards to the question I asked you, if you say the form is only for viewing, you could simply combine all that information on one unbound form as separate subforms. You would do the same on a report.
 

Lkwdmntr

Registered User.
Local time
Today, 10:09
Joined
Jul 10, 2019
Messages
277
This isn't the first time I have had criticism on the design of my database. This is my first time doing anything like this and although the design is not traditional, it is working well. I appreciate all the constructive criticism and would love to sit down with a few of you and redesign the entire project the way others think it should be. That seems to be a bit unrealistic. If anyone does have the time and patience to work with me, maybe by tackling one specific task per day or week like combining all the daily forms and reports into one useable form, or redesigning all the daily queries into one query. It would be nice to take some of the bulk out of this project. Any help is greatly appreciated.

As far as the current issue, I'll work on pulling the data separately into the form or report as suggested.
 

Lkwdmntr

Registered User.
Local time
Today, 10:09
Joined
Jul 10, 2019
Messages
277
Tried using this DLookup and getting the value for week 1 instead of week 3.

Any advice. I think I need a "&" somewhere or missing quotes.

=DLookUp("[TotActAcntPts]","[PointsQuery]","UserID = [TmpUserID]" And "WeekNumber =3")​
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:09
Joined
Oct 29, 2018
Messages
21,358
Hi. In Access, you can display disparate data on one form or report by using subforms or subreports. To do so, first create a separate form/report for each data source. Then, on a new blank form/report, embed all the other forms/reports you created earlier but don't link them to the main form/report. That would be the easiest approach. But if you want it to look or behave than what you get with it, then it would require additional work and may involve using code. But the point is, what you're asking to do is very possible. The issue with not having a proper table structure is it makes it harder to work with your data, that includes presentation of data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Feb 19, 2002
Messages
42,971
If you want us to look at the database, you will need to load it on this site rather than using a "free" service which we don't trust.

Without knowing what your schema actually is, it is hard to make suggestions. Based on what others who have looked have said, I'm guessing that it isn't normalized and probably should be before you move on.

To get something like the excel report, you would use a crosstab query to select the date range you want for the headers across the top. This is how we "pivot" a properly normalized table into something that looks like a spreadsheet.

The "totals" might have to be subreports if they are not just summing rows of the data section at natural breaks in key data fields.
 

Lkwdmntr

Registered User.
Local time
Today, 10:09
Joined
Jul 10, 2019
Messages
277
Is there any service or site you do trust? The database is over 4MB zipped. I agree I need to take the bulk out of it and would love to approach this more professionally, but I can't risk deleting anything without replacing it and testing its functionality.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
have you tried compact/repair before zipping?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
but I can't risk deleting anything without replacing it and testing its functionality.
take a copy - and delete table data in the copy, then compact, then zip.

If you are using attachments, delete them or if necessary leave a couple to illustrate your issue - attachments are a huge consumer of mB's and ideally should be avoided
 

Lkwdmntr

Registered User.
Local time
Today, 10:09
Joined
Jul 10, 2019
Messages
277
Thanks, Micron. I'm not sure how you did that, but thanks.
 

Users who are viewing this thread

Top Bottom