Access 2010 Database Help! All records not showing up in Report.

[ericlinna]

I've spent hours trying to research this before asking a forum. I've read a few books and went through many tutorials. What I'm trying to do is develop a weekly progress report for employees. I get data from 3 different data sources. One reports their usage of their self-reliance tools, one reports customer interactions and one reports the raw data like number of calls and things like that. The report should spit out a summary of the week, month-to-date- 90 day rolling and year-to-date. I've been successful with getting all of this data.

Here's my problem....the customer interactions report won't always have data for that employee for the week. If there isn't any data, their just flat out removed from the report. Is there a way to automatically put a null value here? My current work around is figuring out which employees aren't on the report and then going into the customer interaction report to enter in their employee number and putting a 0 as their interaction score so that field in the report will show 0 and it will allow them to be on the report.

Right now I let Access do all the coding and I use the WYSIWYG to set most of everything up. I've put in some expressions to calculate percentages, but that's about it.

 

[kjv1611]

Does the underlying query also leave out your missing records? Otherwise, is there any filter on the form?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

REPORT - not form... REPORT... argh!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[ericlinna]

I'm currently combining all of the tables via import. The way it's setup now is I have SEVERAL different tables. Each metric has it's own table for every month. For example, I have Metrics_12_22-1_21, VOC_12_22-1_21, Efficiency_12_22-1_21, Metrics_1_22_2_21 and so on..... On the Report, I just have text boxes that reference those tables. The only queries I make is for efficiency. It sums the productive minutes and divides it by the sum of scheduled minutes. When someone reports that an employee is missing, I find that their ID number isn't listed on the VOC table. I add their ID number to the VOC table and viola! their page will show up on the report.

What's happening is if their employee ID isn't on one of the tables included in the report, it leaves them off of the report all together.

 

[ericlinna]

I'm thinking this will come into play with my new database as well. I'm trying to run a query to pull the prior 90 days of data, sum one field and sum another so when I add it to the report, I can create a field that will divide the two to create a percentage. The same goes for the query, if an employee doesn't have data for one of the months, instead of just identifying the ID isn't present and giving them a 0, it leaves them out of the query.

 

[dhookom]

Each metric has it's own table for every month

Is this the actual structure in your database or are you combining the data into a single table?

I just have text boxes that reference those tables

How do you reference tables in text boxes?

kjv1611 made reference to your underlying query. Can you provide the SQL view of the report's record source?

Duane
Hook'D on Access
MS Access MVP

 

[ericlinna]

As of now, each monthly metric has it's own table. To get the text box to reference the field, I drag it into the report from the right side "add existing field list"

How do I get the SQL view of the report? I tried to right-click the Report's tab, but it only gives Report View, Layout View, Design View and Print View.

 

[dhookom]

Every report (and form) has a record source property. This should provide want I am asking for. It should be something like:

SELECT ....
FROM ....
JOIN ....

IMO, separate tables is probably a mistake but I could be wrong.

Duane
Hook'D on Access
MS Access MVP

 

[ericlinna]

When I click on the field in the report and select Control Source, it shows PASS_RATE_METRICS_9_22-10_21 "PASS_RATE" is the field and "METRICS_9_22-10_21" is the table. For the Current Pass Rate it is PASS_RATE I'm thinking it doesn't name the table because it was the first field I entered.

I agree that I need to use a consolodated table, but due to being new to this, the demand for the reports and not yet knowing enough to build efficient queries, I'm stuck building separate tables and essentially performing the query myself when I get the data from our data sources.

 

[dhookom]

Apparently you didn't find the report's Record Source property.

Can you provide your significant table and field names?

Duane
Hook'D on Access
MS Access MVP

 

[ericlinna]

Where would I go to find the Reports record source property?

Significant Table #1:

EmployeeProfile (Table Name)
Employee_LastName (Field Used)
Employee_FirstName(Field Used)
Employee ID (Field Used)
Supervisor (Field Used)

Significant Table #2:

METRICS_9_22-10_21 (Table Name)
Employee ID (Field not Used, but creates the link)
Pass_Rate (Field Used)
Appts_met (Field Used)
Scheduled_minutes (Field Used)
Productive_minutes(Field Used)

Significant Table #3:

VOC_9_22-10_21 (Table Name)
Employee_LastName (Field not Used, but creates link since this imported data doesn't contain an Employee ID)
Employee_FirstName (Same as above)
CustomerSatifaction(Field Used)


Each of these tables have similar tables, but for different date ranges. [Table Name]_12_22-10_21 [Table Name]_7_22-10_21

The report is a table grouped by supervisor and sorted by employee.

The Columns for the report are Metric - 30 Day - 90 Day - YTD - Current. The Rows under Metric are Pass Percentage - Appts met - Scheduled Minutes - Productive Minutes - Efficiency (calculated field by dividing Productive minutes by Scheduled Minutes and Customers Satisfied.


I hope this helps, being new, I'm not sure what information is significant to help me solve my problem. For all I know, there isn't a solution and I will have to manually enter the employee information into the VOC tables and leave the rest blank for them to be included on the report.

I wish there was a way for the report to know that every employee listed on the employee profile table needs a report and if there's any data missing, to just put a 0 or "N/A" in that field.

 

[dhookom]

I'm not sure how to tell you regarding finding the report properties dialog since we don't know what version of Access. If you have the report open in design view, you should be able to double-click the small black square to the left of the horizontal ruler and above the vertical ruler.

Regarding your table structures, I would combine all tables that have a similar structure and add a column/field for the date.

If records aren't showing, you might need to use a LEFT or RIGHT JOIN.

Duane
Hook'D on Access
MS Access MVP

 

[ericlinna]

I'm using Access 2010. Using your method, I found the SQL....Thanks! Even though I found it, I think I'm more confused than I was before! Anyways, here it is:

SELECT TechProfile.TECH_ID, TechProfile.SUPERVISOR, VOC.COMCAST_TOTAL_SCORE, METRICS.TECH_ID AS TECH_ID_METRICS, METRICS.TOTAL_JOBS, METRICS.TC_COUNT1, METRICS.INSTALL_COUNT, METRICS.DISCO_COUNT, METRICS.SRO_COUNT, METRICS.TOTAL_APPTS, METRICS.MISSED_APPTS, METRICS.APPT_MET_RATE, METRICS.TC_APPTS, METRICS.TOTAL_TC_REPEAT_COUNT, METRICS.TC_REPEAT_RATE, METRICS.TOTAL_SOI, METRICS.SOI_PCT, METRICS.TTL_INSTALL_COUNT, METRICS.TOTAL_FTSRT_JOBS, METRICS.FTSRT_ATTMTS, METRICS.Last, UT1.LEVELS_FOUND, UT1.LEVELS_PASSED, UT1.PASS_PCT, HC.NUM_REQ_HC, HC.NUM_HC_RAN, HC.PCT_HC_RAN, VOC.TECH_SCORE, VOC.CCG, VOC.WELCOME_KIT, VOC.TOTAL_SURVEYS, TechProfile.First, HC.PCT_HC_PASSED, HC90.[Sum Of NUM_REQ_HC], HC90.[Sum Of NUM_HC_RAN], HC90.[Sum Of NUM_HC_PASS], HCYTD.[Sum Of NUM_REQ_HC] AS [Sum Of NUM_REQ_HC_HCYTD], HCYTD.[Sum Of NUM_HC_RAN] AS [Sum Of NUM_HC_RAN_HCYTD], HCYTD.[Sum Of NUM_HC_PASS] AS [Sum Of NUM_HC_PASS_HCYTD], EFFICIENCYQ.[Sum Of Scheduled_Minutes], EFFICIENCYQ.[Sum Of Total_Productive_Minutes], METRICSYTD_10_21.[Repeat Rate], METRICSYTD_10_21.[SOI on Truck Rolled Installs], METRICSYTD_10_21.[   Total Met Rate], METRICSYTD_10_21.[   Tech Tool Attempt], METRICS90_7_10.TC_REPEAT_RATE AS TC_REPEAT_RATE_METRICS90_7_10, METRICS90_7_10.SOI_PCT AS SOI_PCT_METRICS90_7_10, METRICS90_7_10.APPT_MET_RATE AS APPT_MET_RATE_METRICS90_7_10, METRICS90_7_10.FTSRT_ATTMTS AS FTSRT_ATTMTS_METRICS90_7_10, [METRICS YTD_10-21].APPT_MET_RATE AS [APPT_MET_RATE_METRICS YTD_10-21], [METRICS YTD_10-21].TC_REPEAT_RATE AS [TC_REPEAT_RATE_METRICS YTD_10-21], [METRICS YTD_10-21].TOTAL_SOI AS [TOTAL_SOI_METRICS YTD_10-21], [METRICS YTD_10-21].FTSRT_ATTMTS AS [FTSRT_ATTMTS_METRICS YTD_10-21], [METRICS YTD_10-21].SOI_PCT AS [SOI_PCT_METRICS YTD_10-21], [VOC_90_10-21].TECH_SCORE AS [TECH_SCORE_VOC_90_10-21], [VOC_90_10-21].COMCAST_TOTAL_SCORE AS [COMCAST_TOTAL_SCORE_VOC_90_10-21], [VOC_YTD_10-21].COMCAST_TOTAL_SCORE AS [COMCAST_TOTAL_SCORE_VOC_YTD_10-21], [UT_90_10-21].PASS_PCT AS [PASS_PCT_UT_90_10-21], [UT_YTD_10-21].PASS_PCT AS [PASS_PCT_UT_YTD_10-21], EFFICIENCY_90.[Sum Of Scheduled_Minutes] AS [Sum Of Scheduled_Minutes_EFFICIENCY_90], EFFICIENCY_90.[Sum Of Total_Productive_Minutes] AS [Sum Of Total_Productive_Minutes_EFFICIENCY_90], EFFICIENCY_YTD.[Sum Of Scheduled_Minutes] AS [Sum Of Scheduled_Minutes_EFFICIENCY_YTD], EFFICIENCY_YTD.[Sum Of Total_Productive_Minutes] AS [Sum Of Total_Productive_Minutes_EFFICIENCY_YTD], [METRICS_CUR_10-29].TC_REPEAT_RATE AS [TC_REPEAT_RATE_METRICS_CUR_10-29], HC_CUR.PCT_HC_RAN AS PCT_HC_RAN_HC_CUR, HC_CUR.PCT_HC_PASS, EFFICIENCY_CUR.Scheduled_Minutes, EFFICIENCY_CUR.Tech_Efficiency, UT_CUR.PASS_PCT AS PASS_PCT_UT_CUR, VOC_CUR.COMCAST_TOTAL_SCORE AS COMCAST_TOTAL_SCORE_VOC_CUR, EFFICIENCY_CURRQ.[Sum Of Scheduled_Minutes] AS [Sum Of Scheduled_Minutes_EFFICIENCY_CURRQ], EFFICIENCY_CURRQ.[Sum Of Total_Productive_Minutes] AS [Sum Of Total_Productive_Minutes_EFFICIENCY_CURRQ]
FROM EFFICIENCY_CURRQ INNER JOIN (VOC_CUR INNER JOIN (UT_CUR INNER JOIN (EFFICIENCY_CUR INNER JOIN (HC_CUR INNER JOIN ([METRICS_CUR_10-29] INNER JOIN (EFFICIENCY_YTD INNER JOIN (EFFICIENCY_90 INNER JOIN ([UT_YTD_10-21] INNER JOIN ([UT_90_10-21] INNER JOIN ([VOC_YTD_10-21] INNER JOIN ([VOC_90_10-21] INNER JOIN ([METRICS YTD_10-21] INNER JOIN (METRICS90_7_10 INNER JOIN (METRICSYTD_10_21 INNER JOIN (EFFICIENCYQ INNER JOIN (HCYTD INNER JOIN (HC90 INNER JOIN (HC INNER JOIN (UT1 INNER JOIN (METRICS INNER JOIN (VOC INNER JOIN TechProfile ON VOC.LAST = TechProfile.LAST) ON METRICS.TECH_ID = TechProfile.TECH_ID) ON UT1.TECH_ID = TechProfile.TECH_ID) ON HC.TECH_ID = TechProfile.TECH_ID) ON HC90.TECH_ID = HC.TECH_ID) ON HCYTD.TECH_ID = METRICS.TECH_ID) ON EFFICIENCYQ.TECH_ID = TechProfile.TECH_ID) ON METRICSYTD_10_21.TECH_ID = TechProfile.TECH_ID) ON METRICS90_7_10.TECH_ID = TechProfile.TECH_ID) ON [METRICS YTD_10-21].TECH_ID = TechProfile.TECH_ID) ON ([VOC_90_10-21].LAST = TechProfile.LAST) AND ([VOC_90_10-21].FIRST = TechProfile.FIRST)) ON ([VOC_YTD_10-21].FIRST = TechProfile.FIRST) AND ([VOC_YTD_10-21].LAST = TechProfile.LAST)) ON [UT_90_10-21].TECH_ID = TechProfile.TECH_ID) ON [UT_YTD_10-21].TECH_ID = TechProfile.TECH_ID) ON EFFICIENCY_90.TECH_ID = TechProfile.TECH_ID) ON EFFICIENCY_YTD.TECH_ID = TechProfile.TECH_ID) ON [METRICS_CUR_10-29].TECH_ID = TechProfile.TECH_ID) ON HC_CUR.TECH_ID = TechProfile.TECH_ID) ON EFFICIENCY_CUR.TECH_ID = TechProfile.TECH_ID) ON UT_CUR.TECH_ID = TechProfile.TECH_ID) ON (VOC_CUR.LAST = TechProfile.LAST) AND (VOC_CUR.FIRST = TechProfile.FIRST)) ON EFFICIENCY_CURRQ.TECH_ID = TechProfile.TECH_ID;

 

[dhookom]

I don't care much for the table structure and naming. I don't understand how you can have a field named "[ Tech Tool Attempt]". How do you get a field name to begin with spaces?

Regardless, if you want all records to display, you need to find some help on LEFT and RIGHT JOINS. These select all records from one side of the join and matching records from the other side of the join. I assume you always want every record from TechProfile.

Duane
Hook'D on Access
MS Access MVP

 

[ericlinna]

Finally! The field [ Tech Tool Attempt] has been eluding me for a week! I added a table, realized that I hadn't changed the column names and removed it. Because I added and removed it, everytime I'd go to view the report, I Access would ask me for the values of those fields. I've got them deleted now! Thanks!

With the LEFT and RIGHT JOINS, I'm assuming this can only be done in the SQL view? Best place to learn about the Joins on Microsoft Access' site?

You are correct. I need every record from TechProfile to be shown whether or not the other table has information for it.

Where else is a good place for me to get information on Access? It seems like on the internet that it's just bits and pieces and Access for Dummies has gotten me to this point, but I need more knowledge to get more of what I want.

Thanks again for all the help and any additional assistance anyone can provide!

 

[dhookom]

You create the LEFT and RIGHT JOIN in design view by double-clicking the join lines and setting the appropriate option.

There are lots of tutorials and other resources on the web. If you are looking for a good book list, check John Viescas' page. If you want to find some good tutorials, search on "Chrystal Long Access Tutorials" on youtube.

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

By the way.. one thing you could do if you want to work with such queries in SQL view (Design view is probably much easier for something like this), then I'd suggest copying the SQL Access built to notepad or another text editor, and using Find/Replace to put Aliases throughout rather than full table names. At least in my opinion, that always makes it easier to read and work with..

So, if you used Aliases for table names, you'd do something like...

From MyFancyTable1 ft1
Join MyFancyTable2 ft2
Join OtherTable1 ot1

And then any time referenced in the SELECT, ORDER, GROUP clauses, you could replace the tablename plus period with the alias plus period..

So if you did that for MyFancyTable1, you'd do a find replace..

Find = MyFancyTable1.
Replace with = ft1.

But with this one, I'd highly suggest sticking with design view - it's a mess t o look at for sure.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[ericlinna]

I agree that SQL is a mess to look at! I about had a heart attack once I figured out how to find it and got a good look at it!!

Where do I create these aliases? I'm assuming I have to name them somewhere so Access knows which tables I'm talking about.

Thanks to everyone for the assistance! I appreciate it!

 

[kjv1611]

See these references about using an Alias in SQL:

http://www.w3schools.com/sql/sql_alias.asp

http://www.1keydata.com/sql/sqlalias.html

I'll type an example, though...

Let's say this is your original SQL Statement:

SELECT Fruit.Name, Fruit.Size, Fruit.Color
FROM Fruit
WHERE Fruit.Eaten Is Null

You could change that to:

SELECT [highlight]f[/highlight].Name, [highlight]f[/highlight].Size, [highlight]f[/highlight].Color
FROM Fruit [highlight]f[/highlight]
WHERE [highlight]f[/highlight].Eaten Is Null

You can use an Alias in a lot of places.. You can also use it for a Field... which works particularly well if using an Aggregate Function:

SELECT f.Name, MAX(f.Size) [highlight]AS Size[/highlight], f.Color
FROM Fruit f
WHERE f.Eaten Is Null
GROUP BY f.Name, f.Color

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57