1

I'm running into a puzzling glitch with MS Access. When I run an append query on a table with a DataType of Number, instead of assigning the queried value to the cell, it increments the number by the queried value. Here's my code..

INSERT INTO Billing ( BillDate, BillAmount, NumActive )
SELECT #8/14/2018# AS Expr1, 100 AS Expr2, Sum(IIf([Members].[Active]=Yes,1,0)) AS Expr3
FROM Members, Billing;

Basically, I'm counting the number of active members for a certain period (which currently happens to be 34 members) so I can evenly divide the bill. However, when I run the query, starting with an empty Billing table, I get the following results each time:

  • Run 1: Billing.NumActive = Null
  • Run 2: Billing.NumActive = 34
  • Run 3: Billing.NumActive = 68
  • Run 4: Billing.NumActive = 102

Snapshot of my Billing table after 4 queries

I would expect 34 to be inserted each time since the number of active members didn't change. Any ideas why this might be functioning like this?

The BillingID is an AutoNumber, and the PerPerson column is Calculated. For right now, I'm hard coding the date and amount, but both will eventually be populated via a form.

ThatNewGuy
  • 197
  • 11
  • That is amusing. The `select` should be evaluated before the `insert`, so I would classify this as a bug. – Gordon Linoff Aug 14 '18 at 23:06
  • 5
    You're generating a cartesian product. Each time you run the query, your sum is multiplied by the number of records in your Billing tabel. Remove the table Billing from your FROM statement. – Rene Aug 15 '18 at 00:40
  • Thanks for the insight, Rene. – ThatNewGuy Aug 15 '18 at 20:13

3 Answers3

3

Your query is counting the number of active members x times, where x is the number of records in the Billing table. This happens, because your query "cross joins" the Members and the Billing table (creating every possible combination of records of both tables). To avoid this, remove the Billing table from the FROM clause of your query:

INSERT INTO Billing ( BillDate, BillAmount, NumActive )
SELECT #8/14/2018# AS Expr1, 100 AS Expr2, Sum(IIf([Members].[Active]=Yes,1,0)) AS Expr3
FROM Members;
Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
  • Is there a way to see a visual representation of the cross join? – ThatNewGuy Aug 15 '18 at 20:18
  • The visual representation of the cross join is that there is no connection line between the two table symbols in design view of the query. There is no other visual representation. – Wolfgang Kais Aug 15 '18 at 22:25
1

You want to INSERT a single row. The INSERT ... VALUES form makes more sense to me here.

INSERT INTO Billing ( BillDate, BillAmount, NumActive )
VALUES (#8/14/2018#, 100, DCount('*', 'Members', '[Active]=Yes'))

If your issue with that is the query designer converts it to INSERT ... SELECT form, do it without a FROM because you don't want to add as many rows as exist in either Members or Billing, and certainly not as many rows as in the cartesian product of those two tables.

INSERT INTO Billing ( BillDate, BillAmount, NumActive )
SELECT #8/14/2018# AS Expr1, 100 AS Expr2, DCount('*', 'Members', '[Active]=Yes') AS Expr3;

If for any reason you can't get away without a FROM, use a table or query which returns only one row. I use a custom table, Dual, which is guaranteed to hold one and only one row.

INSERT INTO Billing ( BillDate, BillAmount, NumActive )
SELECT #8/14/2018# AS Expr1, 100 AS Expr2, DCount('*', 'Members', '[Active]=Yes') AS Expr3
FROM Dual;
HansUp
  • 95,961
  • 11
  • 77
  • 135
0

As an alternative to using DCount, you could use the standard SQL COUNT function:

INSERT INTO Billing ( BillDate, BillAmount, NumActive )
SELECT #8/14/2018# AS Expr1, 100 AS Expr2, COUNT(*) AS Expr3
FROM Members
WHERE Active = Yes;

because an SQL statement without a GROUP BY clause and the COUNT function will only return a single row.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136