How do I sum/count # of records?

[cafulford]

Hi, I have a query that contains data from a table as well as an calculated field that concatenates 2 text fields together called "carrier_trailer". I am creating a new form based off this query and I want to do the following:

1) Show a screen that sums up the table by this "carrier_trailer" field to show the user the available carrier/trailers than can be printed. Only records that contain a value in this field can be printed. I want them to be displayed with the carrier name and trailer # with the # of records (which are bills of ladings on that trailer) for that carrier/trailer combo listed in parens. For example:

UPS12345 (11) ---> carrier = UPS, trailer # = 12345, 11 bol's
FEDEX343243 (25) ---> carrier = FEDEX, trailer # = 343243, 25 bol's
Freightliner4132 (3) ---> carrier = Freightliner, trailer # = 4132, 3 bol's

2) I would like to be able to have a check box next to each one that if selected, would print those bol's related to that selection.

I have been trying to set up a query based on my original query with an additional expression like but I can not get it to work:

Expr1: [carrier_trailer] & Count([??????])

Can anyone help?

Here is part of the file/query layout.



Thanks!

Charlie

 

[dhookom]

Typically you would use a multi-select list box with a row source like:

SELECT CARRIER_NA, TRAILER_NUM, Count(*) as NumOf
FROM bl_File
WHERE CARRIER_NA + TRAILER_NUM IS Not Null
GROUP BY CARRIER_NA, TRAILER_NUM
ORDER BY CARRIER_NA, TRAILER_NUM;

There are a number of FAQs and web resources for figuring out how to filter a report or query based on a multi-select list box.

Duane
Hook'D on Access
MS Access MVP