Show extra records in report 1

[DoctorJDM]

Slightly odd request from a client.

A report shows activities occurring by Area, each area having a fixed set of HomeType options (say A, B, C, D, E), each HomeType having hours, activites and dates recorded against them.

The report asks for a start and end date and then it's just the total hours that are needed for each HomeType, so the Area footer just contains the Hours total and the Details section is set to invisible.

A typical output grouped by Area shows

Area : East
Home Type Total Hours
A 10
D 7

This is all easy enough.

But the client would like to show the full set of HomeTypes for each area, even though many HomeTypes may have no data recorded against them. So the above would need to look like

Area : East
Home Type Total Hours
A 10
B 0
C 0
D 7
E 0

Is there a neat way of doing this?

 

[ralphralph07]

Hi

Is this report based on a query? If so you could change the join type to show all House Types from the House Type table

Ralph

 

[DoctorJDM]

Thanks Ralph but no joy.

HomeType is just a lookup field at present. Adding the HomeTypes table to the query underlying the report doesn't change the data set for any of the join types.

 

[dhookom]

Keep the HomeTypes table in the query and provide us with the SQL view of the query. Make sure your join is set to include all records from the HomeTypes table.

Duane
Hook'D on Access
MS Access MVP

 

[DoctorJDM]

Thanks

My post was slightly a simplified version so here's the fuller background.

The main form shows data for Supervisors, with a subform containing the Hours recorded against people they supervise.

The subform's underlying query is derived from a qryContacts (Names of the people supervised) and tblHours (spent by these people). So Hours are accumulated in the subform for Names with certain HomeTypes in the various Areas, spending Hours on ActivityTypes on the HoursDates.

The important fields in each are

tblHours qryContacts
Name Name
Hours HomeType
HoursDate Area
ActivityType

The join is currently an Inner Join, and HomeType is a lookup field to a single field table of HomeTypes.

The SQL for the query is

SELECT qryContacts.HomeType, tblHours.Hours, tblHours.ActivityType, tblHours.HoursDate, tblHours.Name, qryContacts.Area
FROM qryContacts INNER JOIN tblHours ON qryContacts.Name = tblHours.Name
ORDER BY qryContacts.HomeType, qryContacts.ExplorerArea;

The report of interest is built on the subform information.
I mistakenly said it grouped by Area, but meant by HomeType. It totals the Hours for all various ActivityTypes (these and the HoursDates are made visible in the report).

The objective is to include those HomeTypes for which there is no data in the report's query, so their hours show 0. Then HomeType shows a list of total hours for every area even if the totals are 0.

Taking the above query and adding in the HomeTypes table connected with an outer join produces an error that it now contains ambiguous outer joins (maybe because the lookup is still there?).

Creating a new query using the original one and the HomeTypes table doesn't lead to the extra fields required, no matter which way the join is set.

Sorry of this sounds horribly complicated.

 

[dhookom]

Create a new based on your previous query. Add the HomeTypes table. You should now be able to join the query to the table as display all records from the table.

Duane
Hook'D on Access
MS Access MVP

 

[DoctorJDM]

Sorry Duane, must be missing something.

I created a new database to replicate what I'm trying to achieve but simplifying to make sure there were no glitches in my original.

As before, Access only allows the HomeType table to be added to the query by creating a new query combining the two. Changing the join creates just a few totally blank additional rows. The number of rows seems to be the difference between the total number of HomeTypes and those already present in the original query.

 

[dhookom]

Share some SQL views. Looking at the Northwind sample database, assuming you want to report the number of times you use a shipping company between two dates:

Create a query "qgrpShipping"

SELECT Orders.ShipVia, Count(Orders.OrderID) AS CountOfOrderID
FROM Orders
WHERE (((Orders.OrderDate) Between #1/17/1997# And #1/22/1997#))
GROUP BY Orders.ShipVia;

[tt][blue]
Ship Via CountOfOrderID
============== ==============
Speedy Express 4
United Package 1
[/blue][/tt]
Then create another query based on the Shippers table and the above query:

SELECT Shippers.ShipperID, Shippers.CompanyName, Shippers.Phone, Val(Nz([CountOfOrderID],0)) AS NumOf
FROM Shippers LEFT JOIN qgrpShipping ON Shippers.ShipperID = qgrpShipping.ShipVia;

[tt][blue]
Shipper ID Company Name Phone NumOf
========== ================ ============== ======
1 Speedy Express (503) 555-9831 4
2 United Package (503) 555-3199 1
3 Federal Shipping (503) 555-9931 0[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[DoctorJDM]

Thanks Duane

That looks to be spot on - I'll adapt it to my needs. Really appreciate your time.

 

[murali2611]

I have a similar problem,

I have data from 2 tables in a LEFT JOIN.

When I ccreate a REPORT I encounter few problems.

Data in first table are shown even if they do not match anything in table 2, which is fine.

I am trying to get the difference between 2 fields and where there is not match the whole amount is difference.

If I do a total at the end of the report, the values are sometimes double of ehat they should be, because sometimes the match for 1 item is twice ion the second table.

How do I solve this ?

 

[dhookom]

You should provide some table structures, sample records, and desired output in your report so we have enough information to help you.

Duane
Hook'D on Access
MS Access MVP

 

[murali2611]

OK

MY SQL QUERY :

SELECT DISTINCT PurchaseOrders.PONumber, PurchaseOrders.SPCode, PurchaseOrders.Date AS PurchaseOrders_Date, PurchaseOrders.Net AS PurchaseOrders_Net, SuppInvoices.[Inv No], SuppInvoices.Date AS SuppInvoices_Date, SuppInvoices.Net AS SuppInvoices_Net
FROM PurchaseOrders LEFT JOIN SuppInvoices ON PurchaseOrders.PONumber=SuppInvoices.POnumber
WHERE (((PurchaseOrders.PRCode)=[Forms].[ProjectReports].[projcode].[Value]));



My Problem is when I do the reports :

If I create a header and group by PurchaseOrders.Net then I can put PurchaseOrders.Net in the header then they only appear once, in the header:

Peter Janes (Supplier Name)
PO Number 6090 26/12/2008 £8351.00
Inv No 6377 £8351 Invoice to Receive £0
Total Supplier Invoices £8351

Peter Zidane (Supplier Name)
PO NUmber 7091 26/12/2008 £10000
Inv No 5637 £5000
Inv No 5673 £2500
Total Supplier Invoices £7500 - Invoice to Receive £2500

Chord Engineering
Po Number 8998 25/12/2008 £5000


GRAND TOTALS PurchaseOrders.Net £33351



My GRAND TOTAL is wrong and it is over by £10000 because there are 2 invoices matching the PO, so the report has when I sum it up included it twice.

And also when there is no match from the other table (supplier invoices) then it does not give me the difference - (Invoices to receive)

I hope you can help me.

 

[dhookom]

Do the same thing I suggested earlier. Create a query like:

[red][qtotPONet][/red]

SELECT PO.PONumber, Sum(PO.Net) AS SumPONet
FROM PurchaseOrders PO
WHERE PO.PRCode=[Forms].[ProjectReports].[projcode];

Then include this query in your report's record source and join the PONumber fields. Add the SumPONet field to your report's field list and use it.

Duane
Hook'D on Access
MS Access MVP

 

[murali2611]

I can only add 1 query to the record source of the report.

How do I get the other values ?

 

[murali2611]

DO I rewrite my query ?

 

[dhookom]

Your report's record source query can contain multiple tables/queries. You would need to join the PONumber fields.

Duane
Hook'D on Access
MS Access MVP

 

[murali2611]

It does not add the query that I have just added to the recordsourcce of the report.

 

[dhookom]

You can add any table or query you want (other than action queries) to the design view of the record source of your report. There are probably 3-4 different methods for doing this.

Duane
Hook'D on Access
MS Access MVP