Search code examples
sqlms-accessinsert-into

Why does DataType Number increment inside MS Access Append (INSERT INTO) query?

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.

Solution

  • 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;