Ms-access – How to create columns with Count and percentage using MS Access Query

You have have to try something a little loopy, such as the following:

Reports have a property called Tag
Open the Report in Design View
Open the Property Sheet and Select the 'Other' Tab
The Tag property is on the bottom of the list

You can access that Tag as follows:

Reports("Report Name").Tag

Simply replace the [Identifier] with either Reports("Report Name").Tag or Me.Tag

That way, if you open the query, you will still be prompted to input the UserID

When you open the Report, set Me.Tag to whatever number you want. How to you set it in the first place ?

On the property sheet, Click Event Tab. In the OnLoad Event, click the three dots (ellipses). Menu pop ups. Click Code Builder. VBA opens on with :

Private Sub Report_Load()

End Sub

Now put that setting in (say the UserID is from a form):

Private Sub Report_Load()

   Me.Tag = Forms("Form Name")("Field Name")

End Sub

Give it a Try !!!

I'm guessing the id column is actually not important in your expected result. In which case, you just need to do a grouping and a summation:

select Items, sum(Quantity) as TotalQty
from MyTable
group by Items