Basic help (i think)

[steveee]

Hi!

I'm building a db in access for a school project, and was wondering how to
make the value for a field the same as the number of records with the same
value in another field.
e.g.
DvdTitle = Pulp fiction
StockLev = 1

DvdTitle = Finding Nemo
StockLev = 2

DvdTitle = Finding Nemo
StockLev = 2
etc.

also (you can tell how little I've used access now...) how to say a currency
value(the price of each dvd)*a numeric value (the stock level) in the field
StockCost. I presume it is just "=Price*StockLe v" in default value field.

Thankyou very much (in advance)
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003

[hal boyles]

If table1 is the name of your table...

This a MAKE-TABLE Query

SELECT DISTINCTROW Table1.DvdTitle , Table1.StockLev , Count(*) AS [Count Of
Table1] INTO Table2
FROM Table1
GROUP BY Table1.DvdTitle , Table1.StockLev ;

Then This query will populate the count field

UPDATE DISTINCTROW Table1 INNER JOIN Table2 ON Table1.DvdTitle =
Table2.DvdTitle SET Table1.StockLev = [Table2]![Count Of Table1];
About the Calculation...y our evaluation is correct but it is not good
practice to stor calculations. Calculations can be displayed in form,
reports and queries as you have described. Use the Builder option in the
Query tool to properly build the formula.

[steveee]

> If table1 is the name of your table...

This a MAKE-TABLE Query

SELECT DISTINCTROW Table1.DvdTitle , Table1.StockLev , Count(*) AS [Count Of
Table1] INTO Table2
FROM Table1
GROUP BY Table1.DvdTitle , Table1.StockLev ;

Then This query will populate the count field

UPDATE DISTINCTROW Table1 INNER JOIN Table2 ON Table1.DvdTitle =
Table2.DvdTitle SET Table1.StockLev = [Table2]![Count Of Table1];
About the Calculation...y our evaluation is correct but it is not good
practice to stor calculations. Calculations can be displayed in form,
reports and queries as you have described. Use the Builder option in the
Query tool to properly build the formula.

Thankyou! It turns out that I need many tables (i thought through the
assignment and found it was a crap specification). What i was trying to do
wasn't with queries, buti see now that that is the only way to add the full
functionality that was required. (teacher wanted a one table database with
customers, dvds, allsorts on the same thing - how stupid?)
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003

[TC]

I suggest you read:

http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC

steveee <sm************ *************** ***@ve.com.inva lid> wrote in message
news:Th******** **********@news fep4-glfd.server.ntl i.net...

Hi!

I'm building a db in access for a school project, and was wondering how to
make the value for a field the same as the number of records with the same
value in another field.
e.g.
DvdTitle = Pulp fiction
StockLev = 1

DvdTitle = Finding Nemo
StockLev = 2

DvdTitle = Finding Nemo
StockLev = 2
etc.

also (you can tell how little I've used access now...) how to say a currency value(the price of each dvd)*a numeric value (the stock level) in the field StockCost. I presume it is just "=Price*StockLe v" in default value field.

Thankyou very much (in advance)
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003