SQL question about counting the existence of a distinct value in a table

Michael R

Hi all.

This is an SQL question, so I hope this is the right place to ask it.

I'm intersted in "compressing" a table to distinct values of a field or several fields, and having another field with the amount of existence of each distinct value in the following way:

original table:
Fname.....LnameInitial
----------....-----------------
John.........D.
John.........B.
George.....C.
Sam.........D.
Sam.........D.
Tom..........A.
Tom..........A.
Tom..........A.

"compressed" table or query:
Fname...LnameInitial..Amount
---------...---------------..-----------
John........D. ................1
John........B. ................1
George....C. ................1
Sam........D. ................2
Tom.........A. ............... 3

I have no idea on how to create such a query. Going through different SQL commands I haven't found anything appropriate for this kind of task. It does seem to me like a useful query to know how to create.

Also, if it's not too much to ask, I'm intersted in creating exactly the opposite query that will create the original table from the compressed one.

Thanks for you attention, Michael.

msquared

Try this ...

Expand|Select|Wrap|Line Numbers

 
SELECT Fname, LnameInitial, Count(LnameInitial) As Amount

FROM TableName

GROUP BY Fname, LnameInitial;

Mary

Michael R

Quote:

Originally Posted by mmccarthy

Try this ...

Expand|Select|Wrap|Line Numbers

 
SELECT Fname, LnameInitial, Count(LnameInitial) As Amount

FROM TableName

GROUP BY Fname, LnameInitial;

Mary

Thanks a lot, Mary. It works. What I don't understand Is why the count function count exactly the distinct rows.

What about an opposite SQL command that will transform the table in second sketch to a table in a first sketch? Is it at all possible?

msquared

Quote:

Originally Posted by Michael R

Thanks a lot, Mary. It works. What I don't understand Is why the count function count exactly the distinct rows.

This is where the Group by comes in. Group by Fname and then by LnameInitial makes the values distinct and the Count then counts the number of LnameInitial for each group.

Quote:

What about an opposite SQL command that will transform the table in second sketch to a table in a first sketch? Is it at all possible?

What exactly are you looking for ?

Michael R

Quote:

Originally Posted by mmccarthy

This is where the Group by comes in. Group by Fname and then by LnameInitial makes the values distinct and the Count then counts the number of LnameInitial for each group.

What exactly are you looking for ?

Thanks for the explanation.

I have a table of Distribution items, and sometimes there's an amount next to the item. So I would like to create a query that will "expand" this into Distribution items where one record represents one item.

Also I can handle this in an inverted way, by first creating a table where one record is one item, and the create a query that will count the distinct ones, like with the solution you gave me.

I need the both views for that table.

So know I am able to preform the second option. I would like to be able to preform the first option too.

msquared

Quote:

Originally Posted by Michael R

Thanks for the explanation.

I have a table of Distribution items, and sometimes there's an amount next to the item. So I would like to create a query that will "expand" this into Distribution items where one record represents one item.

Also I can handle this in an inverted way, by first creating a table where one record is one item, and the create a query that will count the distinct ones, like with the solution you gave me.

I need the both views for that table.

So know I am able to preform the second option. I would like to be able to preform the first option too.

Micael can you give me the tablename and fieldnames for the distributions table.

NeoPa

I'm watching with interest as I can't think of a way (short of creating a finite table of possible data to OUTER JOIN to - but as a separate preparatory step. I'm not even sure I could find a way to make that work).

Michael R

Quote:

Originally Posted by NeoPa

I'm watching with interest as I can't think of a way (short of creating a finite table of possible data to OUTER JOIN to - but as a separate preparatory step. I'm not even sure I could find a way to make that work).

NeoPa, you're on to something, I already have heard of that way from some other place. Creating a finite table of possible data is fine with me.
It was suggested there to have a table Integers with i from 1 to lets say 100

Expand|Select|Wrap|Line Numbers

 SELECT 1 as Amount , Fname

FROM Integers INNER JOIN myTable

WHERE i between 1 and Amount

However, there must be a mistake here because Fname isn't on Integers.
So it didn't work.

SQL is a pretty cool language, and the solutions to problems using it sometimes come in a sort of neat/weird hierarchical way.

msquared

Quote:

Originally Posted by NeoPa

I'm watching with interest as I can't think of a way (short of creating a finite table of possible data to OUTER JOIN to - but as a separate preparatory step. I'm not even sure I could find a way to make that work).

Michael

What is the max number the count is likely to reach and is there some reason we can't use code to do this as it is pretty impossible to find a way to do it in pure sql.

Mary

NeoPa

Technically, it is possible to create a very complicated UNION query in code as long as the max number of records is not too enormous. This would not be a good idea unless absolutely necessary. I suggest that using RecordSet VBA, as Mary is about to explain, is a preferable idea.

NeoPa

Quote:

Originally Posted by Michael R

NeoPa, you're on to something, I already have heard of that way from some other place. Creating a finite table of possible data is fine with me.
It was suggested there to have a table Integers with i from 1 to lets say 100

Expand|Select|Wrap|Line Numbers

 SELECT 1 as Amount , Fname

FROM Integers INNER JOIN myTable

WHERE i between 1 and Amount

However, there must be a mistake here because Fname isn't on Integers.
So it didn't work.

SQL is a pretty cool language, and the solutions to problems using it sometimes come in a sort of neat/weird hierarchical way.

I'm afraid that what you have here looks more like a VBA type loop rather than SQL. I don't think that concept can work in SQL I'm afraid. I'd be interested to learn otherwise but until then see the other recent posts.

Michael R

Quote:

Originally Posted by mmccarthy

Michael

What is the max number the count is likely to reach and is there some reason we can't use code to do this as it is pretty impossible to find a way to do it in pure sql.

Mary

Well, I don't know how it is possible, I've attempted to create a loop in VBA in the following way:

Expand|Select|Wrap|Line Numbers

 Dim strSQL as String

strSQL = "INSERT INTO myTable (Feild1, Feild2) VALUES ('" & me.txbText1 & "', '" & me.TxbText2 & "'');"

For i%=1 to me.Amount

   DoCmd.RunSQL strSQL

Next i%

But it fails to retrieve a unique amount from each record of the form.

NeoPa

I've been chatting with Mary & I've thought of a way it could be done with SQL after a table gets created or populated in VBA code.
You need a table with records in it up to the number which is the largest number in your table. Each record should have a field with an incremental number in it (Rec1 = 1; Rec2 = 2; etc). I will try to put together the SQL from this point shortly. Mary is looking at the VBA part for you.
The MetaData of the table you need to create is :

Expand|Select|Wrap|Line Numbers

 Table Name=tblCount

Count; Numeric

msquared

Expand|Select|Wrap|Line Numbers

 
Public Function populateCountTable()

Dim maxCount As Integer

Dim x As Integer
 
  DoCmd.RunSQL "DELETE * FROM tblCount;"

  maxCount = DMax("[Amount]","TableName")
 
  For x = 1 To maxCount

    DoCmd.RunSQL "INSERT INTO tblCount (Count) VALUES (" & x & ");"

  Next x
 
End Function

NeoPa

Expand|Select|Wrap|Line Numbers

 SELECT T.Fname,T.LnameInitial

FROM TableName AS T, tblCount AS C

WHERE C.Count<=T.Amount

Michael R

Mary, thanks, I didn't use the code you've gave me, as the code NeoPa gave is enough for "expanding" query of that table so each item can have it's own record.

NeoPa, the code works magically!

Thanks a lot to both of you.
:)

NeoPa

Michael, don't mean to argue but using Mary's code is definitely the better way to go. Manually entering 100 records is fine until you find data that goes beyond that.
If you know absolutely that'll never happen then perhaps it's ok (but I'd use the code anyway as it's more complete).

Glad it's working anyway :)

Michael R

Heh, kick me, but I just don't understand why it works. How exactly the criteria of C.Count <= T.Amount fetches a number of rows, if you'll be so kind to explain?

Michael R

Quote:

Originally Posted by NeoPa

Michael, don't mean to argue but using Mary's code is definitely the better way to go. Manually entering 100 records is fine until you find data that goes beyond that.
If you know absolutely that'll never happen then perhaps it's ok (but I'd use the code anyway as it's more complete).

Glad it's working anyway :)

Actually, I tried Mary's code, and it didn't work, as the amount value is different for each record, and it doesn't preform the opperation correctly. Retrieving the max amount value is also unlogical here.

I didn't explain the whole picture, and I appologize for that. There is a temprorary table with items and amounts for each item which is added to a big table. By your method it is added to the bigger table with the item amounts, and then I can use your query to expand the data so each item will have it's own record. Mary's method is adding data already in that way (of each item having its own record) but it doesn't work.

NeoPa

Quote:

Originally Posted by Michael R

Actually, I tried Mary's code, and it didn't work, as the amount value is different for each record, and it doesn't preform the opperation correctly. Retrieving the max amount value is also unlogical here.

Michael,
No! No! No!
You seem to misunderstand this. Wait until I explain the process then tell us it's not right (You'll need some good explaining though ;)).
This will have to wait until tomorrow though as it's after 01:30 & I want to go to bed :(

NeoPa

Quote:

Originally Posted by Michael R

Actually, I tried Mary's code, and it didn't work, as the amount value is different for each record, and it doesn't preform the opperation correctly. Retrieving the max amount value is also unlogical here.

I didn't explain the whole picture, and I appologize for that. There is a temprorary table with items and amounts for each item which is added to a big table. By your method it is added to the bigger table with the item amounts, and then I can use your query to expand the data so each item will have it's own record. Mary's method is adding data already in that way (of each item having its own record) but it doesn't work.

I'll get to this in more detail tomorrow, but if it doesn't work, then the specification is wrong. You can't tell us half the story then wonder why it's not exactly right.
Again, I'll check this over again tomorrow.

BTW Mary was creating the code to my specific instruction. It was a necessary part of the whole.

Michael R

Thanks to you both:)
I didn't know it will go as far as creating an overall solution.

Michael R

To make things clearer here's the sum-up of everything in this thread:

- TempTable
(Lname, Fnameinitials, Amount)

where Lname and FnameInitials are an item.

- MainTable with the same MetaData

The data (items with amounts to each item) is being selected on a form that uses TempTable, then by VBA code is added to MainTable, then deleted from TempTable.

There's a requierment to be able to view MainTable data in both 'compressed' and 'expanded' way, the 'expanded' way is having a record for each item (without amounts).

I asked at first of ways of 'expanding' and 'compressing' data of MainTable via query. Later, Mary gave me a suggestion to add the already 'expanded' data(or, beacuse she didn't know that there's a TempTable, maybe what she ment is to compress the data in MainTable somehow) beacuse then it is easier to 'compress' it via query she gave me that doesn't require a finite number of integers.

With the neat query NeoPa gave me, but which does use a finite number of integers, I'm able to add data from TempTable to MainTable with amounts (in the compressed way) and then view it in the expanded way via the query.

msquared

Quote:

Originally Posted by Michael R

I asked at first of ways of 'expanding' and 'compressing' data of MainTable via query. Later, Mary gave me a suggestion to add the already 'expanded' data(or, beacuse she didn't know that there's a TempTable, maybe what she ment is to compress the data in MainTable somehow) beacuse then it is easier to 'compress' it via query she gave me that doesn't require a finite number of integers.

Michael

You misunderstood my post. I posted that code at the request of NeoPa as a way of populating the tblCount Table that he uses in his query. It is not a proposed solution to your question. I was simply helping NeoPa out with his proposed solution.

Mary

NeoPa

In the FROM clause there is an OUTER JOIN (Otherwise known as no join). Both tables are listed but separated by a (,) rather than any type of join.
This results in a recordset where every record of table A is crossed with every record of table B. As the records in the count table (tblCount) are sequentially numbered, the WHERE clause ensures that only the correct number of records are produced from this little sub record set.

Expand|Select|Wrap|Line Numbers

  TempTable
 Lname     Fnameinitials Amount

Smith     Bob           3

Jones     Jim           1
 
 tblCount
 Count

1

2

3

4
 
 Results before WHERE clause
 Lname     Fnameinitials Amount Count Count<=Amount?

Smith     Bob              3      1     Y

Smith     Bob              3      2     Y

Smith     Bob              3      3     Y

Smith     Bob              3      4     N

Jones     Jim              1      1     Y

Jones     Jim              1      2     N

Jones     Jim              1      3     N

Jones     Jim              1      4     N
 
 Results after WHERE clause
 Lname     Fnameinitials

Smith     Bob

Smith     Bob

Smith     Bob

Jones     Jim

Michael R

Since this thread hadn't been on the first page of the forum, I thought it had been abandoned. Thanks so much Mary and NeoPa for you work. Thanks NeoPa for the very neat query and your explanation of how it works, you've helped me so much!

Cheers, guys and gals.

NeoPa

Strangely enough, it was only after Mary asked me to have a look at it that I thought I had a solution, only to realise it was impossible (logically).
After giving up on the idea, we continued chatting and thrashing through how to explain that, when the final idea came to me via something Mary said.

I'm very pleased to have been involved as it's another interesting way of creating something a little outside-the-box from a SQL construct.

SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

re: SQL question about counting the existence of a distinct value in a table

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about