Counting records in query and populating new field with total results..

[JonWales]

Hi there, I'm new to MS Access...

Can anybody please help me with the following issue:-

I have a Query table and I need to count the instances of records "L1", "L2"
and "L3" in the 'Query Table' for each field (70+) and create / display new
fields called "Totals_of_L1" , "Totals_of_L1", "Totals_of_L1" (sub totals
counts of L1. L2 & L3 per record/row ?

For example:
Field_1 __ Field_2 __ Field_3 __ Field_4 __ Field_5____Total_of_L1__
Totall_of_L2__Total_of__L3
_L1 _________L1 ______L1_____L2_______
L3_____________3___________1___________1____
_L2 _________L2 ______L3_____L1_______
L3_____________1___________2___________2____
_L3 _________L3 ______L3_____L2_______
L3_____________0___________1___________4____

...and so on for each row etc...

 

[tina]

being new to Access, you've made a common "newbie" mistake - "committing
spreadsheet" on your table. Access tables *look* similar to Excel
spreadsheets, but they are very different. suggest you STOP developing your
database now, and read up on relational design principle before you go any
further. the time you invest now will repay you 1000-fold when you begin
developing again using proper structure that can fully leverage the amazing
power of the Access software. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Lord Kelvan]

i think your database structure is bad so it will create problems like
complex queries there would eb a much eaiser way to do it if it was
built right but.

you would need something like

SELECT Querytable.field1, Querytable.field2, Querytable.field3,
Querytable.field4, Querytable.field5, IIf([field1]="l1",
1,0)+IIf([field2]="l1",1,0)+IIf([field3]="l1",1,0)+IIf([field4]="l1",
1,0)+IIf([field5]="l1",1,0) AS [total of l1], IIf([field1]="l2",
1,0)+IIf([field2]="l2",1,0)+IIf([field3]="l2",1,0)+IIf([field4]="l2",
1,0)+IIf([field5]="l2",1,0) AS [total of l2], IIf([field1]="l3",
1,0)+IIf([field2]="l3",1,0)+IIf([field3]="l3",1,0)+IIf([field4]="l3",
1,0)+IIf([field5]="l3",1,0) AS [total of l3]
FROM Querytable;

hope this helps

Regards
Kelvan