query problem in access

[balllian]

i have the following cell in excel but i want to get this to work in access but im not getting the same results in access as from excel

This is the cell in excel

=ROUND(B3/1212,0)

in what format can i get this to work in a query in access.

Thanks in advance.

 

[Golom]

Nothing wrong with the formula. Same one runs in Access (but drop the "="). You will need to reference a field in Access where you have a cell reference (B3) in Excel.

Round([SomeField] / 1212, 0)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[dhookom]

Access doesn't have a "B3". Do you have some examples that you can't get to work? You haven't provided any samples of numbers or results with what you expected.

Your expression seemed to work in a query that I created. I opened Northwind and created a query with SQL

SELECT Round(([UnitPrice]*1000)/1212,0) AS RndToNoDec
FROM Products;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[balllian]

im doing this and it doesnt work.

([CountOfCategory]/[Sumofhits],0)

am i doing this wrong??

 

[lespaul]

are countOfCategory and SumOfHits field names in the TABLE or alias names in the QUERY? you can't use aliases within the same query you have to use the exact expression again.

This WILL NOT WORK:
SELECT COUNT(*) AS CountOfCategory, SUM(*) As SumOfHits, CountOfCategory/SumOfHits FROM TableName

You have to use:
SELECT COUNT(*) AS CountOfCategory, SUM(*) As SumOfHits, COUNT(*)/SUM(*) FROM TableName


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

Just that?

Or are you doing this

[COLOR=red]Round[/color]([CountOfCategory]/[Sumofhits],0)

?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[balllian]

they are aliases within the same query. this is the sql that im using.

SELECT Monthly_hits_final_april06.Category, Monthly_hits_final_april06.Subcategory, Count(Monthly_hits_final_april06.Category) AS CountOfcategory, Sum(Monthly_hits_final_april06.Hits) AS SumOfHits, Max(Monthly_hits_final_april06.Field8) AS [Hits Max]
FROM Monthly_hits_final_april06
GROUP BY Monthly_hits_final_april06.Category, Monthly_hits_final_april06.Subcategory;

 

[Golom]

SELECT    Category, Subcategory, Count(Category) AS CountOfcategory, 
          Sum(Hits) AS SumOfHits, Max(Field8) AS [Hits Max],
          Round ( Count(Category) / Sum(Hits) , 0 ) As [Ratio]

FROM      Monthly_hits_final_april06 

GROUP BY  Category, Subcategory;

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]