Numbers Help

  • Thread starter Thread starter Maciel
  • Start date Start date
M

Maciel

I have a Table with this structure:
Field - Data Type
Date - Date/ Time
Stock 1 - Number
Stock 2 - Number
Stock 3 - Number
Stock 4 - Number
Stock 5 - Number
Stock 6 - Number
The stock in one or two digits and I want create a Query
for show me the number that most appear : Like give this
kind of result
04 appears 39 Times
19 appears 27 Times
02 appears 12 Times
And I don't have any idea how to build this.
Any suggestions
Thanks

Maciel

I using Access 97
 
Hi Maciel,

I think a union query might just be what you are looking
for. Something along the line of this.

<code start>

Select [Stock 1] as StockID, Count([Stock 1]) as
StockCount From tblFoo
Union Select [Stock 2] as StockID, Count([Stock 2]) as
StockCount From tblFoo
Union Select [Stock 3] as StockID, Count([Stock 3]) as
StockCount From tblFoo
Union Select [Stock 4] as StockID, Count([Stock 4]) as
StockCount From tblFoo
Union Select [Stock 5] as StockID, Count([Stock 5]) as
StockCount From tblFoo
Union Select [Stock 6] as StockID, Count([Stock 6]) as
StockCount From tblFoo

<code end>

this will produce a table like this:

StockID StockCount
----------------------
02 12
04 39
19 27


Hope this helps,
Brig
 
Thanks but I received a error message, do you know how I
solve this ?

Invalid SQL
Statement;expected 'DELET', 'INSERT', 'PROCEDURE','SELECT',
OR 'UPDATE'.
More Help
OK

Thanks
Maciel
-----Original Message-----
Hi Maciel,

I think a union query might just be what you are looking
for. Something along the line of this.

<code start>

Select [Stock 1] as StockID, Count([Stock 1]) as
StockCount From tblFoo
Union Select [Stock 2] as StockID, Count([Stock 2]) as
StockCount From tblFoo
Union Select [Stock 3] as StockID, Count([Stock 3]) as
StockCount From tblFoo
Union Select [Stock 4] as StockID, Count([Stock 4]) as
StockCount From tblFoo
Union Select [Stock 5] as StockID, Count([Stock 5]) as
StockCount From tblFoo
Union Select [Stock 6] as StockID, Count([Stock 6]) as
StockCount From tblFoo

<code end>

this will produce a table like this:

StockID StockCount
----------------------
02 12
04 39
19 27


Hope this helps,
Brig
-----Original Message-----
I have a Table with this structure:
Field - Data Type
Date - Date/ Time
Stock 1 - Number
Stock 2 - Number
Stock 3 - Number
Stock 4 - Number
Stock 5 - Number
Stock 6 - Number
The stock in one or two digits and I want create a Query
for show me the number that most appear : Like give this
kind of result
04 appears 39 Times
19 appears 27 Times
02 appears 12 Times
And I don't have any idea how to build this.
Any suggestions
Thanks

Maciel

I using Access 97

.
Click to expand...
.
Click to expand...
 
Sorry,

I left the order by out. Try this.

<code start>

Select [Stock 1] as StockID, Count([Stock 1]) as
StockCount From tblFoo Order By [Stock 1]
Union Select [Stock 2] as StockID, Count([Stock 2]) as
StockCount From tblFoo Order By [Stock 2]
Union Select [Stock 3] as StockID, Count([Stock 3]) as
StockCount From tblFoo Order By [Stock 3]
Union Select [Stock 4] as StockID, Count([Stock 4]) as
StockCount From tblFoo Order By [Stock 4]
Union Select [Stock 5] as StockID, Count([Stock 5]) as
StockCount From tblFoo Order By [Stock 5]
Union Select [Stock 6] as StockID, Count([Stock 6]) as
StockCount From tblFoo Order By [Stock 6]

-----Original Message-----
Thanks but I received a error message, do you know how I
solve this ?

Invalid SQL
Statement;expected 'DELET', 'INSERT', 'PROCEDURE','SELECT' ,
OR 'UPDATE'.
More Help
OK

Thanks
Maciel
-----Original Message-----
Hi Maciel,

I think a union query might just be what you are looking
for. Something along the line of this.

<code start>

Select [Stock 1] as StockID, Count([Stock 1]) as
StockCount From tblFoo
Union Select [Stock 2] as StockID, Count([Stock 2]) as
StockCount From tblFoo
Union Select [Stock 3] as StockID, Count([Stock 3]) as
StockCount From tblFoo
Union Select [Stock 4] as StockID, Count([Stock 4]) as
StockCount From tblFoo
Union Select [Stock 5] as StockID, Count([Stock 5]) as
StockCount From tblFoo
Union Select [Stock 6] as StockID, Count([Stock 6]) as
StockCount From tblFoo

<code end>

this will produce a table like this:

StockID StockCount
----------------------
02 12
04 39
19 27


Hope this helps,
Brig
-----Original Message-----
I have a Table with this structure:
Field - Data Type
Date - Date/ Time
Stock 1 - Number
Stock 2 - Number
Stock 3 - Number
Stock 4 - Number
Stock 5 - Number
Stock 6 - Number
The stock in one or two digits and I want create a Query
for show me the number that most appear : Like give this
kind of result
04 appears 39 Times
19 appears 27 Times
02 appears 12 Times
And I don't have any idea how to build this.
Any suggestions
Thanks

Maciel

I using Access 97

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top