Querie to count values on 2 tables

[ferggie]

I am new to access and have been teaching myself. I am using 2003 due to work requirements. I have 2 tables and can’t get one to count the other. The 1st one is list values that table 2 uses to fill 10 fields. I want to take the list and see how many times the value is used.

Table 1 “CIN”

ID | CIN .......| Course Short Title CDP.. LS
16 | C-130-3406 | AN/AQS-13F SYS I ..548N. HELO I
17 | C-150-3010 | AN/ASM-175 EMTC ...0510. HELO I
18 | C-198-3042 | RTCASS OP MAINT ...03NY. ATE
19 | C-198-3043 | CASS ADV MAINT/ ...7286. ATE
20 | C-198-3044 | CASS COMMON .......7288. ATE


Table 2 “personnel” used a look up table to fill in the CIN INFO

............Primary
ID |LS .....CIN....... 2nd CIN ...3rd CIN... 4th CIN.to.9th CIN
7 .|HELO I .C-102-4051 C-130-3406 C-150-3010
8 .|HELO I .C-130-3406 C-150-3010 C-102-9408
9 .|ATE ....C-198-3070 C-198-3043 C-198-3042 C-198-3044
10 |ATE ....C-198-3044 C-198-3043

What I am looking for called Table CIN COUNT

CIN .......CDP .LS ....COUNT
C-130-3406 548N HELO I.. 2
C-150-3010 0510 HELO I.. 2
C-198-3042 03NY ATE .....0
C-198-3043 7286 ATE .....2
C-198-3044 7288 ATE .....2

 

[PNGBill]

Looks to me like your tables are not setup properly.

Normally one Table does not Count another Table.

You use a query to find the records and count the result.

Each occurrence of the output will be a new record, not a new field.

If you want to select all the Smith entries in the Telephone book then the query will give you 300 records with Smith as the family name.
The same query could be changed slightly to instead give 300 as the count for Smith. ie, One record that tells you 300 is the result.

Tables are for storing Data not for storing the result of this data.

Even a Sales Database at a store doesn't keep all the information of your purchase. It stores the SalesDate, CustomerID, SalesQTY, ProductID and SalePrice.
The Invoice final amount is calculated as required. It is simply Qty x Price.

Have a good look at your Database and refer to Normalizing a Database in Help and or Google. Also search this Forum on Normalizing.

Can you attach a screen shot of your Relationships ?

 

[ferggie]

I know that I am in 2007 in the Scr shoot but at work it is 2003 I am using

 

[PNGBill]

Your Personnel Table is huge compared to the others. This is not important in itself but can point to too many fields and that some at least should be in there own table.

I suggest you explain more what the database is all about and some of the fields you want covered.

eg. If a school you would have Students - StudName, StudAge, StudDOB, StudAdrress, StudClass etc - all in StudentsTable

StaffTable would have StaffName, StaffPosition, StaffTelNum, StaffDOB, etc

CourseTable would have CourseName, CourseGrade, CoursePrimaryStaffID, CourseHomeRoomID etc

ClassRoomTable would have ClassRoomNum, ClassSeatCapacity, ClassDescription, ClassTelNum etc

Take a good look at your Personnel Table and explain what some of the fields are for, if you think they should still be in the one table.

Think of a Database as being a Group of Lists with each list covering a separate issue eg Staff, Students, Class Rooms and Courses.

Each of the lists only needs one Connection to any of the others (may have more but not always)

CourseTable has one field called CourseHomeRoomID which is the link to ClassRoomTable.

StudClass in StudentTable would allow a link to the Teacher or the Class and then the teacher could be linked to the class.