Showing ALL the records contained in a table in a report, including all with null values.

[TazoTazmaniac]

Hi all,

I want to make a report in my database that would include all fields in a table, but not sure if it's even possible.

So basically, we deliver large items out to people, and sometimes they also want what we call 'ancillaries'. The ancillaries are listed in a table (tblAncillaries) and are allocated in another table (tblAllocatedAncillaries) which is connected to tblDelivery using DeliveryID (PK of tblDelivery), which in turn in connected to a client details table (tblClientDonorContact).

I want a report that would be printed everytime anyone wants ancillary items containing some of the details of the client and then the WHOLE list of ancillary items, not just the ones that are allocated to them. This would all be connected using DeliveryID. The reason why I want all of them is because they are likely to add items on to the list after the delivery is set in place and I don't want to be printing off a new slip after they change everything each time. Also it helps with packing up the items if they have all the items on the list (don't ask me why, it's how it's been requested!)



How would I go about this? I have thought about manually writing up the list of items using labels and then using IIf functions and dlookups, but that would result in a very slow form surely, take a lot of time to create and also be fairly longwinded to change if the list was to ever change. I'm open to ideas!

Thank you in advance.

[ranman256]

Yes, you can put all fields in a report, but the report has a limit on # fields, and space available on the sheet.
your report query just has to pull them all.

[NTC]

A report is based upon a record set which is either a table or query. One can definitely design a query to have all records, including nulls. I don't fully follow your data structure but in general terms one can control the join line between tables while in query design. the normal join line means 'where there is a match in both tables' ; but if you right click on that line you can alter that to be 'all records of 1 table even if there is null in the other' and that join line will then look like an arrow. this is what you should experiment with.

[TazoTazmaniac]

Thank you for your replies.

So I've played around with the relationships and got the list of all the items and then whatever is allocated for each person. However, how do I only get one person's allocation to show up while still seeing the whole list? If I put the deliveryID in the criteria, all it's going to come up with is what's allocated to them and not the nulls. What can I look at here to get the result I want?

[Dave D]

Have you tried massaging the join properties of your report's record source?

When you view the report's "record source" property, double-click the lines connecting the tables. This will display the Join Properties dialog box; select an option that is different than default and see how that works.

It may be easier to try this in a test query, adding tables as you go, and then copying the sql into your report.

[TazoTazmaniac]

Yes I have, and the problem happened as described in the last post. I'm not sure what I want to do is possible so may just have to have a ridiculously slow form full of dlookups.

[Dave D]

Have you looked at having a sub-report that has all items from tblAncillaries, removing those items that are already in tblAllocatedAncillaries, so you don't get duplicates? A query that uses the 'Not In' clause could do it.

[TazoTazmaniac]

Thank you for your reply. Again, though, I can get it right generally, but as soon as I want to make it specific for a particular delivery, I fall flat. Tried 'Not In', but 'Not Exists' seemed to work better, but I couldn't work out what to do next. This was my code:

SELECT AncillaryID, AncillaryItem
FROM tblAncillaries
WHERE NOT EXISTS (SELECT AncillaryID, DeliveryID FROM tblAllocatedAncillaries WHERE tblAncillaries.AncillaryID=tblAllocatedAncillaries.AncillaryID);