totaling across multiple columns

[KungFuGod]

suggestions please...

i have a query that finds specific data in 5 columns.
How to i combine the resulsts into a single point?

five columns are CF1, CF2, CF3, CF4, CF5
The criteria for each is

, allowing the user to pick which code to search for.
 
data is txt (ie. CF-B, CF-B1 etc...)
 
is there a way to find the total across all 5 in  a single step?
 
Thanx very much for your assistance.

 

[rodmc]

You want to total text???? or do you mean you want to combine the text into a string??

for "concatenating" text see here
http://www.techonthenet.com/access/functions/string/concat.php


for summing/totally numeric data
Expr: IIf(Isnull([CF1]),0,[CF1])+IIf(Isnull([CF2]),0,[CF2]) etc etc

 

[KungFuGod]

rodmc

Thanx for the reply.

i am looking for specific txt in each of the columns and totaling the occurrances.

How many CF-B in each of the columns and adding them for a total.

an another example.
book sales, customers purchase 1-5 books...
I need to know the total number of a specific book over a period of time.
the specific book could be in any of the 5 stored columns..

thanx very much for your help​

 

[rodmc]

you want to count using a crosstab then, not sure about your data structure though, sounds like you've got an un-normalized table.

 

[KungFuGod]

i have a large table of data... the 5 columns contain various codes... each row can have more than 1 code..
ie...
row 1 CF1: CF-B1 CF2: CF-R CF3: CF-B2
row 2 CF1: CF-R
etc..
my query pulls these 5 columns and shows only rows that have the specific code.

if i want to sum all the CF-R, there would be 2 in this example.

is there a way to automate?

Thanx

 

[rodmc]

if each row can have more than one code, then you should be splitting that part off into another table and creating a one to many relationship with your original being the "one" and the new table being the "many"

think of an author who has written many books, the idea would be to have the author and his details in one table and then the books he has written in another

The author table would have an ID for each author, this ID would be the foreign key in the books table that would tie the many books to the one author that he/she had written.

Author table
Author1 - Irvine welsh
Author2 - Hunter S Thompson
Author3 - JRR Tolkien


Books table
BookID1 - Author1 - Trainspotting
BookID2 - Author1 - Acid House
BookID3 - Author1 - Maribou Stork Nightmares
BookID4 - Author2 - Fear and Loathing in LV
BookID5 - Author2 - The Great Shark Hunt
BookID6 - Author3 - The Hobbit
BookID7 - Author3 - The Lord of the Rings

Do you see how it works????

You really need to look at your table relations before going any further, if your still not getting it then Allen Browns explanation will explain better than I could
http://allenbrowne.com/casu-06.html

 

[KungFuGod]

Thanx...I appreciate the insight.

My table is fixed as it pulls from a much larger database.
I can query each of the 5 columns individually or a as a sing query of all 5.

I am looking to have a single action (push a button) that will ask what data you want, then display the total number of hits.

Using your example, from the 1500 purchases last year, how many "The Hobbit" were sold, recognizing the data is stored as First book, Second Book, Third Book... as a single entry transaction.

Thanx very much for your effort and patience... i usually do this with Excel, but want to try and make it work with Access....

 

[boblarson]

for summing/totally numeric data
Expr: IIf(Isnull([CF1]),0,[CF1])+IIf(Isnull([CF2]),0,[CF2]) etc etc

rodmc - A shorter way than that is to use the NZ function

Expr:Nz([CF1],0) + Nz([CF2],0) + Nz([CF2],0) ...etc.

 

[Pat Hartman]

My table is fixed as it pulls from a much larger database

You can normalize it locally to make your queries simplier.

 

[rodmc]

rodmc - A shorter way than that is to use the NZ function

Expr:Nz([CF1],0) + Nz([CF2],0) + Nz([CF2],0) ...etc.

Good point Bob!

 

[KungFuGod]

Pat, you suggested: You can normalize it locally to make your queries simplier.
Please explain.
I am relatively new with Access... thanx

 

[Pat Hartman]

Normalization isn't an Access concept. It is a relational database concept and it defines the best way to store data so that it isn't duplicated and can be queried efficiently. What you described is called a repeating group. Whoever defined the table decided that there would NEVER be more than 5 of something and so he defined 5 columns to hold instances of the same data. But, in a relational database when you have more than one of something, you have "many" and "many" requires a second table. Once you move the items to another table, you can have as many or as few rows as required.

Since you will be "normalizing" the data each time you import it, you will need to create a series of queries to automate the process. rodmc showed you what the tables should look like.

 

[KungFuGod]

Thanx very much for the help.

I have all 5 columns of data now in a single table with a unique identified for each row.

if i want a user to select a specific data element, how do I create a query to search for the specified data element, and only return the total count?

All suggestions are very much appreciated...