Counting Strings?

[philljp390]

Is this possible to do in a query?

I have the following table (for example):

Field1.......Field2
1.............House, House
2.............House, Car, Car
3.............House, Tree, Car
4.............Car, Car, Car

I want to count those text strings that are the same:

Field1.......Field2
1.............1 House
2.............1 House 2 Car
3.............1 House 1 Tree 1 Car
4.............3 Car

Any help will be much appriciated.

 

[Rich]

It would be easier to do if you normalised the Data first

 

[Mile-O]

Field1.......Field2
1.............House, House
2.............House, Car, Car
3.............House, Tree, Car
4.............Car, Car, Car

Why would you have those sort of values in Field2? That is not atomic.

 

[philljp390]

Sorry, the first table should read:

Field1.......Field2.........Field3.......Field4
1.............House...........House
2.............House...........Car............Car
3.............House...........Tree..........Car
4.............Car...............Car...........Car

Then obviously combine all these values into one field and count them to get:

Field1.......Field2
1.............2 House
2.............1 House 2 Car
3.............1 House 1 Tree 1 Car
4.............3 Car

 

[Mile-O]

As Rich says, you'd still be best to normalise your data before you attempt to do anything with it.

 

[EMP]

In the other thread, your table was normalized?!!

I have a table structured as follows (for example):

Field1----Field2
1--------House
2--------House
2--------Car
2--------Car
3--------Tree
3--------Car
4--------House
4--------Tree

 

[philljp390]

Ive never understood normalisation very well, i thought it would be easier to do it as detailed in this thread. Field1 is the unique key, and I have both those tables in my database.

 

[Mile-O]

Ive never understood normalisation very well

If you don't understand it then it's better to ask questions or for simpler explanations than to disregard it and try to workaround problems in the future such as this one. Due to the repeating group evidenced by Field 2, Field 3, and Field 4 in the earlier post I'd suggest you have a read of my First Normal Form (1NF) FAQ - I tried to make it quite easy to understand. If you have any questions, just ask.

Since you obviously have need for more than specific object (car, house, tree) in the one table then what you have is a many-to-many relationship whereby you need a table for these options and a junction table to join said table and the table you currenty have Fields 1- 4 in.

 

[philljp390]

ok, can we presume the tables are normalised as the structures need to remain as they are. I do realise this is like the opposite to what the textbooks say. The tables are obviously more complex than the example here, but is it possible to do what is shown here? Can I count the strings and combine them ito one field?

 

[Mile-O]

Can you show an example of the actual table? It may make this workaround easier to understand.

 

[philljp390]

Sorry to be a pain here, but i cant really show the actual table. I have gotten pretty far writting this expression:

Expr1: [Field1] & " " & [Field2] & " " & [Field3] & " " & [Field4]

That gets me this:

ID........Expr1
1.........House House
2.........House Car Car
3.........House Tree Car
4.........Car Car Car

Now if only i can count these strings as well.

 

[Mile-O]

It will be made easier if you have a seconf table where these options are selectable from

i.e.

tblObjectType
ObjectTypeID
ObjectType

where ObjectType fills down with Car, Tree, House, etc.

That way, these values can be fed into an unbound array and then the evaluation can be done on each field. It's such a long laborious problem. Do you have such a table?

 

[philljp390]

So each field would have a drop down (combo) list, for you to select the values?

 

[Mile-O]

It would be the smarter method with respect to table design.

 

[philljp390]

ok, bit of a update, Ive got a bit further using this test data:

IDno......Field1.....Field2
1204.....House......House
2304.....Car
3404.....Tree......House
4504.....House

Using this query expression:

DCount("[IDno]","tblTest","[Field1] Or [Field2] ='House'")

I get:

IDno......Expr1
1204.....4
2304.....4
3404.....4
4504.....4

So its counting how many occurrences of "House" there are, but counting the whole coloums where as I want it to count row by row to get:

IDno......Expr1
1204.....2
2304.....0
3404.....1
4504.....1

Does this make some more sense?

 

[Pat Hartman]

The DCount() is NOT working. Your syntax is incorrect. As it is, no matter what value you substitute for "House", including a nonexistant value, the count will be the number of rows in the table.

DCount("[IDno]","tblTest","[Field1] Or [Field2] ='House'")

should be:

DCount("[IDno]","tblTest","[Field1] = 'House' Or [Field2] ='House'")

Relational databases do not provide functions that work "across" the columns of a row. You have a repeating group and as everyone so far has said, your best option is to fix the problem and fixing the problem requires normalizing the table so that each of the repeating columns actually becomes a row in a new table.

To do what you want without normalizing will require you to learn enough VBA to write a function that builds an array to hold each column of the group, sort the array so the values are in alpha order, and then loop through the array and count the duplicates. That's as close as I'll get to providing code for this.

 

[philljp390]

The DCount() is NOT working. Your syntax is incorrect. As it is, no matter what value you substitute for "House", including a nonexistant value, the count will be the number of rows in the table.

DCount("[IDno]","tblTest","[Field1] Or [Field2] ='House'")

should be:

DCount("[IDno]","tblTest","[Field1] = 'House' Or [Field2] ='House'")

This DOES actually work with the code ive been working with.

Relational databases do not provide functions that work "across" the columns of a row.

Yeah, I was working it through in my head and came to this conclusion. Pitty theres not a combination of Excel and Access.

You have a repeating group and as everyone so far has said, your best option is to fix the problem and fixing the problem requires normalizing the table so that each of the repeating columns actually becomes a row in a new table.

I'll have to think through this one some more, as i've always struggled with normalisation.

That's as close as I'll get to providing code for this.

Thanks

 

[Rich]

If you post an example of what your db does and what fields etc. relate to what I'm sure someone here will help suggest a structure

 

[philljp390]

ok, ive done up some test data more suited to my situation that has vehicles leaving and arriving at different depots.

Hopefully this may help.

 

[Jon K]

Once you have normalised your data (see query "qryOne"), you can use a function (which is very similar to the one in your other thread) to concatenate the Vehicles in a second query ("qryTwo").

Note Whenever new vehicle columns are added to the table [Depot Data], you must add them to "qryOne" as well, or they will be left out.