Subdatasheet in Totals Query (1 Viewer)

[MarcusL]

Hi

I have a database which records 'incidents' (ie things going wrong in my place of work).

I have created a totals query (based on a single table) which shows how many incidents have been recorded for each category of incident (eg IT, suppliers, on call etc) in my database.

I want to add a subdatasheet so that clicking the little plus sign will give more details about the incidents in that category.

In properties, I have added the table's name under 'Subdatasheet Name' and added 'Incident ID' (the table's primary key) under both 'Link Child Fields' and 'Link Master Fields'.

However, when I run the query and click the little plus (to expand, for example, incidents in the 'IT' category), I just get a pop up saying 'Enter parameter value Incident ID'.

Can anyone advise how to solve this so that I can see the details of the incidents in the subdatasheet?

Many thanks in advance.

 

[June7]

Really should work with forms, not directly with tables and queries.

Since the aggregate query should not have Incident ID field, cannot link with table on Incident ID. Try Category ID.

 

[CJ_London]

Agree with June, it is a bad idea to interact with tables directly - use forms

and subdatasheets really slow down db performance if you have a large amount of data

Anyway, advice about good design over.

I don't see how your query can have Incident ID in its results - otherwise you would get a full list. Suggest try using category as your link/child/master fields

 

[MarcusL]

Agree with June, it is a bad idea to interact with tables directly - use forms

and subdatasheets really slow down db performance if you have a large amount of data

Anyway, advice about good design over.

I don't see how your query can have Incident ID in its results - otherwise you would get a full list. Suggest try using category as your link/child/master fields

I'm not interacting directly with the table - I'm using a query. Can you describe how using forms would help in this case?

Performance isn't an issue as the database isn't very big (just a few hundred records).

The advice to use 'Category' as the child and master field is great - it would have worked if my problem was as I described above but unfortunately I oversimplified it slightly.

In fact there are two different fields for 'Event Category' and 'Sub Category'. The query I have made shows how many incidents have been recorded for each combination of category and sub category.

So when I use Event Category as the link/child/master, expanding the subdatasheet gives a list of all incidents in that Event Category (regardless of Sub Category). See 'Totals Query 1' image.

And when I use Sub Category as the link/child/master, expanding the subdatasheet gives a list of all incidents in that Sub Category (regardless of Event Category). See 'Totals Query 2' image.

But what I am looking for is the subdatasheet to show only incidents which match for both Event Category and Sub Category (in the example images there are four such records).

I've blocked out bits of data but you should get the idea from these images. Hope I've explained myself clearly. Thanks again

 

[MarcusL]

Really should work with forms, not directly with tables and queries.

Since the aggregate query should not have Incident ID field, cannot link with table on Incident ID. Try Category ID.

Thanks June - I have posted a reply to CJ_London's comment (it's awaiting moderation at the moment). Any help would again be greatly appreciated. Cheers

 

[CJ_London]

I'm not interacting directly with the table - I'm using a query. Can you describe how using forms would help in this case?

it's good practice. You can use a knife as a screwdriver, but better to use a screwdriver.

And you are interacting with the table - its your subdatasheet. I purposely did not mention queries because they have the same potential interaction issues as tables - but because you are using a aggregate query, it is not updateable.

as far as the linking is concerned, with a form/subform arrangement you would use

Event Category;Sub Category

for both your link child/master properties

you'll have to see if that works in a query.

 

[MarcusL]

it's good practice. You can use a knife as a screwdriver, but better to use a screwdriver.

And you are interacting with the table - its your subdatasheet. I purposely did not mention queries because they have the same potential interaction issues as tables - but because you are using a aggregate query, it is not updateable.

as far as the linking is concerned, with a form/subform arrangement you would use

Event Category;Sub Category

for both your link child/master properties

you'll have to see if that works in a query.

Event Category;Sub Category worked perfectly in the query, thanks a lot!

As for the good practice issue. I could make a query that contains the entire table and base the subdatasheet on that? But from what you're saying it sounds like this would still not be good practice because working with queries is a risk.

So my question is - what would be a solution? A form with a subform which can be expanded for each combination of Event Category and Sub Category? How would that be different from a query with a subdatasheet (other than aesthetically)?

And what is the point of queries if using them is poor practice? When should queries be used?

I realise these are quite broad questions - just trying to get as good an understanding as possible. Thanks

 

[CJ_London]

the point is with a form you have control. In particular you can prevent accidental modification or deletion of data. Dealing with tables directly or through queries you have no such control. Once its gone, its gone, there is no undo.

It's fine whilst developing or just a quick 'noddy' app to test something out or for your own entertainment, but as an app which forms part of your corporate process it is not a good way to go.

The purpose of (most) queries is to extract a subset or analysis of data. Every time you open a table, that whole table needs to be brought across - plus subdatasheets if used. With small amounts of data on your local drive, not a noticeable problem, but for large datasets across a LAN you will soon see the performance hit. Using a query reduces the amount of data to be brought across which speeds the whole process. This may not be a problem for you now, but might be in the future if you have learned bad habits.

 

[June7]

Queries manipulate data in ways other than just filtering. The aggregation query you already have is only one example. Then query would be used as source for a form or report or even export. Review http://allenbrowne.com/subquery-01.html

Then there are action queries (DELETE, UPDATE, INSERT).