Counting selections of a combo box

[Bar_NZ]

Hey all, just wondering if there is a way in a query to count what was selected in a combo box from a form, these selections are written back to the main table.

I have many codes, however I need to count how many times each is used in a week, month, year...

I have a query that can grab and display the codes in a report, but not the amount of time's they are used.

The codes go like this:

ID LateCode1 (Through to 13)
1 KRO1
2 KRO4
3 LCT1

ID CancelledCode1 (Through 13)
1 KROx1
2 KROx4
3 LCTx1

and so on..

If anyone can help, I would really appreciate it.

Cheers

Bar_NZ

 

[YNWA]

You need to perform a Count on each ID or code.
This counts my genders:

SELECT dbo.REF_Gender.Gender, COUNT(dbo.REF_Gender.Gender) AS Counts
FROM dbo.tbl_Patients INNER JOIN
dbo.REF_Gender ON dbo.tbl_Patients.GenderID = dbo.REF_Gender.GenderID

REF_Gender is my Gender reference table
Gender is the field Gender within that table
I also have a PK of GenderID in this table

tbl_Patients is my table were all my clients are stored and the table were the GenderID is stored.

So you want something like

SELECT dbo.TBL_LateCodes.LateCode1, COUNT(dbo.TBL_LateCodes.LateCode1) AS Counts
FROM dbo.tbl_MAINTABLE INNER JOIN
dbo.TBL_LateCode1 ON dbo.tbl_MAINTABLE.LateCodeID = dbo.TBL_LateCodes.ID

I assumed that you have a reference table full of codes which I have called LateCodes.

 

[Bar_NZ]

Now comes two stupid questions.

1: Where it says LateCode1, do I have to repeat this through to 13
2: Where does this go, into VB on report??

 

[YNWA]

No, I took lateCode1 from your table view in first post. You say ID and lateCode1, its the field name the code is in.

It goes in a query for SQL.

Attach your DB and I will look at your set up.

 

[Bar_NZ]

Thank you, I really do appreciate this.
Please dont laugh too loud when you open this, i have good hearing, lol.

I think one of my problems is that each late code and cancelled code for that matter has about 15 or 20 lookup's in it's list.

If you have any better ideas how I could re-design this DB feel free to let me know, once you have stopped laughing and picked yourself up off the floor that is.



Cheers

Bar_NZ