Help with DSUM

[weircolin]

Hi

I am wanting to show how many entries are on my database with different
criteria (i.e. How many are professionals etc).

I beleive to do this I should use DSUM, can anyone tell me the best
method to do this?

Thanks

Colin

 

[Nick 'The database Guy']

Hi Colin,

Personally I would use a query. The following sql will need to be
changed to accomodate your field names. But you should be able to see
the logic.

SELECT Count(name) AS CountOfname, ExtraInfo
FROM tblCustomer
GROUP BY tblCustomer.ExtraInfo;

Good luck

Nick

 

[Nick 'The database Guy']

Hi Colin,

Personally I would use a query. The following sql will need to be
changed to accomodate your field names. But you should be able to see
the logic.

SELECT Count(name) AS CountOfname, ExtraInfo
FROM tblCustomer
GROUP BY tblCustomer.ExtraInfo;

Good luck

Nick

 

[Klatuu]

The DSum will return only one value based on the criteria you give it. The
criteria is the same as SQL code. It also add the values in the selected
field. If you want to count the entries based on criteria, then you would
want the DCount. The syntax is pretty much the same. So, if we want to sum
the quantity of widgets that were returned because they were defective:

=DSum("[Qty]", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

If we just want to count the number of returns, regardless of how many were
returned:
=DCount("*", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

 

[weircolin]

Hi

Thanks for this. Would I be right in thinking that with using these
statements I could refer to options on a form?

=DSum("[Qty]", "tblReturns", "[cbowidgetmodel.Value] = '" &
Me.txtWidgetModel & "'")

Or is that bad practise?

Thanks

Colin

The DSum will return only one value based on the criteria you give it. The
criteria is the same as SQL code. It also add the values in the selected
field. If you want to count the entries based on criteria, then you would
want the DCount. The syntax is pretty much the same. So, if we want to sum
the quantity of widgets that were returned because they were defective:

=DSum("[Qty]", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

If we just want to count the number of returns, regardless of how many were
returned:
=DCount("*", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

Hi

I am wanting to show how many entries are on my database with different
criteria (i.e. How many are professionals etc).

I beleive to do this I should use DSUM, can anyone tell me the best
method to do this?

Thanks

Colin

 

[Klatuu]

What do you mean by options on a form?

Hi

Thanks for this. Would I be right in thinking that with using these
statements I could refer to options on a form?

=DSum("[Qty]", "tblReturns", "[cbowidgetmodel.Value] = '" &
Me.txtWidgetModel & "'")

Or is that bad practise?

Thanks

Colin

The DSum will return only one value based on the criteria you give it. The
criteria is the same as SQL code. It also add the values in the selected
field. If you want to count the entries based on criteria, then you would
want the DCount. The syntax is pretty much the same. So, if we want to sum
the quantity of widgets that were returned because they were defective:

=DSum("[Qty]", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

If we just want to count the number of returns, regardless of how many were
returned:
=DCount("*", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

Hi

I am wanting to show how many entries are on my database with different
criteria (i.e. How many are professionals etc).

I beleive to do this I should use DSUM, can anyone tell me the best
method to do this?

Thanks

Colin

 

[weircolin]

Sorry, as in a selection from a combo box.

Thanks

What do you mean by options on a form?

Hi

Thanks for this. Would I be right in thinking that with using these
statements I could refer to options on a form?

=DSum("[Qty]", "tblReturns", "[cbowidgetmodel.Value] = '" &
Me.txtWidgetModel & "'")

Or is that bad practise?

Thanks

Colin

The DSum will return only one value based on the criteria you give it. The
criteria is the same as SQL code. It also add the values in the selected
field. If you want to count the entries based on criteria, then you would
want the DCount. The syntax is pretty much the same. So, if we want to sum
the quantity of widgets that were returned because they were defective:

=DSum("[Qty]", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

If we just want to count the number of returns, regardless of how many were
returned:
=DCount("*", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")


:

Hi

I am wanting to show how many entries are on my database with different
criteria (i.e. How many are professionals etc).

I beleive to do this I should use DSUM, can anyone tell me the best
method to do this?

Thanks

Colin

 

[Klatuu]

Sure, you could put something like this in the control source of the control
you use to display the count:

= DSum("[Qty]", "tblReturns", "[cbowidgetmodel.Value] = '" &
[cboWidgetModel] & "'")

Sorry, as in a selection from a combo box.

Thanks

What do you mean by options on a form?

Hi

Thanks for this. Would I be right in thinking that with using these
statements I could refer to options on a form?

=DSum("[Qty]", "tblReturns", "[cbowidgetmodel.Value] = '" &
Me.txtWidgetModel & "'")

Or is that bad practise?

Thanks

Colin

Klatuu wrote:
The DSum will return only one value based on the criteria you give it. The
criteria is the same as SQL code. It also add the values in the selected
field. If you want to count the entries based on criteria, then you would
want the DCount. The syntax is pretty much the same. So, if we want to sum
the quantity of widgets that were returned because they were defective:

=DSum("[Qty]", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

If we just want to count the number of returns, regardless of how many were
returned:
=DCount("*", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")


:

Hi

I am wanting to show how many entries are on my database with different
criteria (i.e. How many are professionals etc).

I beleive to do this I should use DSUM, can anyone tell me the best
method to do this?

Thanks

Colin

 

[weircolin]

Hi

Thanks for your help. I'll give it a bash.

Sure, you could put something like this in the control source of the control
you use to display the count:

= DSum("[Qty]", "tblReturns", "[cbowidgetmodel.Value] = '" &
[cboWidgetModel] & "'")

Sorry, as in a selection from a combo box.

Thanks

What do you mean by options on a form?

:

Hi

Thanks for this. Would I be right in thinking that with using these
statements I could refer to options on a form?

=DSum("[Qty]", "tblReturns", "[cbowidgetmodel.Value] = '" &
Me.txtWidgetModel & "'")

Or is that bad practise?

Thanks

Colin

Klatuu wrote:
The DSum will return only one value based on the criteria you give it. The
criteria is the same as SQL code. It also add the values in the selected
field. If you want to count the entries based on criteria, then you would
want the DCount. The syntax is pretty much the same. So, if we want to sum
the quantity of widgets that were returned because they were defective:

=DSum("[Qty]", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")

If we just want to count the number of returns, regardless of how many were
returned:
=DCount("*", "tblReturns", "[WidgetModel] = '" & Me.txtWidgetModel & "'")


:

Hi

I am wanting to show how many entries are on my database with different
criteria (i.e. How many are professionals etc).

I beleive to do this I should use DSUM, can anyone tell me the best
method to do this?

Thanks

Colin