Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Aug 2005
    Thanked 0 Times in 0 Posts

    DCount (Access 2000)

    DCount in a query
    How can i sum up all the fields in the query? My query consists of the table products.The first field is Productid, the second is ProductName. The next fields are the quantities available in the different cities.So the are : reg0.reg1,reg2,reg3,reg4, etc.The last field is reg10.How can i sum up all the quntities? So the filed reg which is blank, must consist reg0+reg2+reg3 etc.I think i must use the Dcount function but how to combine it with the fields?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: DCount (Access 2000)

    In the first place, the way your data are organized are not ideal. It would be better to have a table Products with fields ProductID and ProductName, another table Cities with fields CiryID and CityName, and an intermediate table Quantities with fields ProductID, CityID and Quantity. A record in the Quantities table would contain the quantity for a specific combination of a product and a city. This setup makes it much easier to filter and tally records in different ways.

    In your situation, you can use

    reg: Nz([reg0],0)+Nz([reg1],0)+Nz([reg2],0)+...+Nz([reg10],0)

    The Nz function takes care of blank (null) values.

    You must write out all fields explicitly. With 10 cities, this is not so difficult, but if you had 100 cities, it would be very tedious, and with 1000 cities, it would be impossible because an Access table can only have 256 fields. The setup I described above is much more flexible.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts