Left join report not grouping correctly

[Robyn_P]

Hey.

I'm trying to create a report based on a left join query.

I have equipment which have connectors on them. Some of the connectors have data, some don't.

What I want is

Equip 1
Connector 1 data
Connector 2 null
Connector 3 data



Equip 2
Connector 1 etc


What I'm getting is

Equip 1
Connector 2 null

Equip 2
Connector # null

Equip 1
Connector 1 data
Connector 3 data

Etc

The underlying data from the report looks fine in datasheet view. Why is the grouping doing this?

[ranman256]

reports dont have joins.....the query does.
in the query the nulls show the missing data.
depending on the join , it shows where what data is missing in what table.
so if you set the join: show ALL records in Equip1 and some in Equip2


the report uses whatever fields you provide in the query.

[Robyn_P]

Yeah I get that, hence "based on" a query.What I'm asking is why is the "Group on" in the report not grouping correctly? i.e. giving me a group of equipment 1 data that is null followed by a group of equipment 1 data that is not null. Rather that giving me just one group for all the connectors on equipment 1

[Robyn_P]

Anyone? This isn't urgent urgent but it's annoying me. The data the query is based off looks fine, when you order it by equipment you get all the connectors on that equipment, just in the report itself the ordering is wrong making the report unusable. If anything isn't clear in the post above please ask. I've even tried recreating the report from scratch to see if there was some odd corruption with no luck. Thanks Robyn

[andy49]

I'd like to see the sql if we can?

[Robyn_P]

SELECT qryMatesStatusAndCycleCounts.*, tblConnectors.Connector, tblEquipment.Equipment FROM tblEquipment INNER JOIN (tblConnectors LEFT JOIN qryMatesStatusAndCycleCounts ON tblConnectors.ID = qryMatesStatusAndCycleCounts.Connector1ID) ON tblEquipment.ID = tblConnectors.EquipHarnessID;

[isladogs]

There is no grouping the sql shown.
So you must be applying grouping in the report itself.
Open the report in design view - go to Sorting & Grouping & modify as necessary

[Robyn_P]

Yes I know this. That's the point. The report grouping is adding two groups. One with null values and one without. I'm doing some investigations. If I remove the equipment side then the sorting works correctly (i.e. the null values are in line rather than all at the start). I still don't understand why.

[Robyn_P]

Well when I say remove, I'm trying this in a new report, but Access is being super slow so this investigation may take a while. I'll update this thread with what I find. Yes grouping the the report is not working correctly despite the underlying data looking fine. That's the problem.

[Robyn_P]

Sorry didn't mean to sound frustrated. So my test report isn't showing the problem, though I haven't got as far as testing adding the grouping yet. Time to give up on it for the evening and go to the pub instead. Will update tomorrow. I'm starting to think it's a glitch caused by modifying a report that was originally just based off the Mates Query rather than starting from scratch. I still don't understand it, but as no one else on the internet seems to have had this problem before, well I can not be the first person to try and create this type of report. Will check back in tomorrow, way too frustrating, time for wine.

[isladogs]

Suggest that after you've been suitably refreshed you post screenshots of the report in design view & print preview together with the underlying data

[Robyn_P]

Got it!!! It's because I have a further grouping level in the report (above the equipment grouping) on a field called bench which is a field in the query, which is obviously null for half the entries. which was causing the split. Have removed that and voila, problem solved.