Order Report

[Stew]

Hello Board,

Its been awhile since my last post, however I've ran into a snag on my current database that this board has helped me put together. I was asked to implement a order system for new gear that the higher command wants to order for my unit. I have attached a real dummied down version of what I am putting in. I am trying to create a form or report that will show the total count of each item field (in the example I only have Glove, Trouser, and Blouse) for each size option. The only way I can think of doing this is multiple queries for each size option to get counts. However this is completly on realistic as in my actually program I have 29 fields I must get the totals for. I am trying to create a button that shows a form or report with this information and sets the Ordered field to Yes to all the records that it pulls the data from. Any help in how to design this would be greatly appreciated.

[HansV]

It's going to be a mess if you have to keep on adding columns (fields) for each new item. I'd use a multi-table setup, as in the attached version.

OrderReport.zip

[Stew]

I see your point Hans. If I run it like that it just seems I have to add another query for the report to be based off with all the SSN that currently have Ordered = No. However here is my question, I want to set Ordered to Yes once I use a button to generate that report. I'm assuming that I will need to be a SQL string to set that up but I am very unfamiliar with SQL string. Can you give me an idea of where to begin to do that? Thanks alot for your help as always.

[HansV]

You could create an update query based on tblSTAP that sets Ordered to True for all records where Ordered is False. You can then run this query from your code:

' Suppress the prompt to update records
DoCmd.SetWarnings False
' Run the query
DoCmd.OpenQuery "NameOfTheQuery"
' Turn on warnings again
DoCmd.SetWarnings True