Report no longer shows all selected data

[pernic80]

Hi All,

I have a Weekly Order Status report that I send out every Monday. So far, the report has been showing all orders that have been placed (arranged by employee responsible for placing the order, etc) I then filter the report by a Yes/No Item Received Box and email to concerned employees based on a macro.
When I opened it today, after not having opened it for a week (I send this report out on Mondays) It would only show 2 employees names instead of the entire report. Any ideas why this might have happened?

 

[vbaInet]

How are you setting the filters? Are you using the WHERE argument of the OpenReport method to do so?

 

[pernic80]

I would select the appropriate filters after the report was generated. Then I would send out the version reflecting the filtering. There should be no filters in place otherwise. Any ideas? When I open the report in design mode the information appears as it always has, only now when I switch back to report view I only get to view two employees info as opposed to the entire company from which I then select info on the orders from that week. Any suggestions on what to look for on the property sheet or elsewhere would be greatly appreciated. I just cannot seem to find what has gone wrong/changed. I did create a switchboard with macros that when pressed on the switchboard automatically bring up the "Weekly Order Status Report" and/or email the Weekly Order Status Report to a pre-selected group of employees. Please help, I am at my wits end with this one and would REALLY prefer not to have to redo the form!

 

[vbaInet]

A report in design view does not display data. So maybe you're referring to Print Preview mode. When it shows the two employees are you sure the navigation bar at the bottom does not indicate there are more pages?

 

[pernic80]

I know it is not in print preview mode. Plus the pages indicate that it is page 1 of 1. What I meant is that in design mode, all of the fields that I added when I created the report are as as they have been since I created the report. Normally when I would be/switch back to Report View mode it would show all data associated with those fields. Now it only shows two employees. I would like it to go back to showing ALL employees and orders, shipping info etc associated with those employees orders and then I could filter from there. Any suggestions would be greatly appreciated. Thank You!

 

[vbaInet]

Have you looked in the record source property of the report? Copy and paste that here let me see.

 

[pernic80]

SELECT [Employees].[EmployeeID], [Employees].[FirstName], [Employees].[LastName], [Order Details].[OrderID] AS [Order Details_OrderID], [Order Details].[ProductID], [Assets].ProductName, Orders.[EstimatedDeliveryDate], [Orders].[OrderID] AS Orders_OrderID, Orders.[Item Received], Orders.DateSupplyRequestReceived, Orders.DateRequired, Orders.OrderDate, Orders.Backorders, [Order Details].[Backorder Details], [Order Details].[Item Received] AS [Item Received_Order Details], [Order Details].Quantity, [Order Details].[Shipping Comments], RequestsAwaitingApproval.EmployeeID AS EmployeeID_RequestsAwaitingApproval, RequestsAwaitingApproval.AwaitingApprovalID FROM (Assets INNER JOIN ((Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID) ON Assets.[ID]=[Order Details].ProductID) INNER JOIN RequestsAwaitingApproval ON Employees.EmployeeID=RequestsAwaitingApproval.EmployeeID;

 

[vbaInet]

It would appear that the two employees that are showing are the ONLY ones with associated Order details or Asset details. The Inner Join restricts the records to that.

Open the query in design view and if only those two employees appear, this is the case.

 

[pernic80]

I do not have a query specifically for Weekly Order Status. I built the report off of tables. I'm not sure I know what you mean. Do I need to create a query?

 

[vbaInet]

Click on the elipsis button in the record source property and the query designer grid will come up. Right click and open it in Datasheet view.

 

[pernic80]

Yes! Found it! You are right the 2 names that have been coming up are the only ones present in the query. Now how do I change it so it shows all names?

THANK YOU SO MUCH

 

[vbaInet]

Ok, so on the query design grid, you can see the lines that join all the tables. You need to change the join type to include ALL records from Employees so that the arrow points away from Employees.

Let me see a screenshot of the query grid.

 

[pernic80]

ok here you go

 

[pernic80]

opps I had to up my post count to attach image

 

[pernic80]

Uploaded with ImageShack.us

 

[vbaInet]

The main focus is your relationships. Can you spread the tables out so I can see how they are linked.

 

[pernic80]

Uploaded with ImageShack.us

 

[vbaInet]

Now you need to change the joins to Left or Right joins in such a way that ALL employees are shown. I'm guessing you know how to change the joins?

 

[pernic80]

Which joins need to be changed? Under the joint properties which # should be selected? Thanks again this is very helpful

 

[vbaInet]

That's the one. The number will depend on which table is on which side but what you should be looking for is the following:

Employees -> Orders:
ALL Employees

Employees ->RequestsAwaiting:
ALL Employees

Orders -> Order Details:
ALL Orders

Order Details -> Assets:
ALL Order Details

Those are the joins.