I agree with Minty. You need to redesign/rebuild your dB. As Minty pointed out, what happens if you need to add another (or 3) badges?
Answer is, you have to modify the table, then the queries, then the forms, then the code, then the reports! Yep, that is a TOTAL rebuild....
You also need to have a better naming convention. I was really confused looking at this SQL:
Code:
SELECT [tbl_Badge1] ,Count(*) AS [Badge Total] FROM [qry_badge_stat] WHERE [tbl_Badge1] IS NOT NULL GROUP BY [tbl_Badge1] UNION ALL
WHY, I wondered, was the table placed at the beginning of the SQL??? Whaaattt???
Finally looked at your dB and realized that "tbl_Badge1" is a FIELD name!
And I also hope you have the dB split into a FE and a BE, AND every used has a COPY of the FE on their computer.
---------
3 rules of programming:
1) Back up
2) Back up
and
3) BACK UP!
I usually create a back up around every 10 minutes (sometimes sooner)... it only takes losing everything once to understand back ups are your friend.
Speaking of which: I got my first computer, an Apple II, and was typing in a BASIC program (at 1 am). After 2 hours of typing, I got up to get a drink.
When I touched the computer again, a spark jumped the gap and scrambled the memory. AND NO BACK UPs!! I had not saved once in those 2 hours.
Nothing I could do , so I shut down and went to bed.
---------
I have a dB that has a table with over 8,000 employees, with the main table approaching 1.5 million records. Being on a network, the larger the main table became, the slower the response of the forms/reports.
I was directed to move/change the BE from Access to SQL Server Express (soon to be SQL Server Standard).
So now I am done with alpha testing and into beta testing.
Once everything was working (more or less) correctly, I started converting the Access queries to T-SQL queries and stored procedures on the server to reduce the number of records transferred across the network.
And lots of studying!!! Data types changed, had to learn about Views, stored procedures, stored functions and started working on getting my head around Transactions.
---------
I would advise you design your NEW table structures/relationships on paper, whiteboard, cardboard, sticky notes, etc, BEFORE creating anything in Access. Add test data and start fixing the queries, forms, code and reports. Once all is working right, create the queries and code to transfer the real data. Once the data transfers correctly, delete the data and transfer real data one last time. (Probably over a long weekend.)
---------
I used dry erasable markers on the windows in the office (yes, I have a "Beautiful Mind" - but everyone just laughs at me ) to do table design, then for writing code. Works great!