Using an append Query to input basic parameters - 0 rows 1

[nedstar1]

Hi friends,

I'm using Access to build a system to partially automate a quotation process for my business. I want to input a few parameters, such as quantities, and then have these stored as values to calculate from in a report or form, haven't decided which.

I understand how to get Access to prompt for a parameter, and I have created a basic query (append) to add the values to a table I called Quotetemp, which I figured was a good first step; from there I could find the value easily, right?

When I run the query, it asks me for the parameters as expected, and then reports that it is about to append 0 rows. Shouldn't it be one row? What have I done wrong?

Please let me know if this isn't enough information.

 

[BNPMike]

Always first build the query as a select. You can then check it actually selects the records you expect. Then turn it into an Append. If it now says 0, I would have thought it would also give you a reason why and/or maybe create an error table so you can get a clue about what's upseting it.

 

[Golom]

No way to know without seeing the SQL. One possibility is that you are running the query more than once. The first time it works but after the append doesn't work because the record with that primary key already exists.

 

[nedstar1]

Here's what I have:
SELECT QuoteAmounts.Amount1, QuoteAmounts.Amount2, QuoteAmounts.Amount3, QuoteAmounts.Amount4
FROM (ItemMaster INNER JOIN QuoteAmounts ON ItemMaster.ID = QuoteAmounts.ID) INNER JOIN QuoteTemp ON ItemMaster.ID = QuoteTemp.ID
WHERE (((QuoteAmounts.Amount1)=[Enter first amount]) AND ((QuoteAmounts.Amount2)=[Enter second amount]) AND ((QuoteAmounts.Amount3)=[Enter third amount]) AND ((QuoteAmounts.Amount4)=[Enter fourth amount]));

What I had expected it to do is allow me to enter the four values, after which they would be appended to the QuoteTemp table as a new record. Since it is not really pulling any data from any field in any table, I made a dummy table called QuoteAmounts to use as my basis.

Clearly I'm in over my head.

 

[Golom]

A SELECT statement extracts information from tables and/or queries. It does not insert or change data in them. You need an INSERT statement of the form
[tt]
INSERT INTO QuoteTemp (Amount1, Amount2, Amount3, Amount4)
VALUES ( [Enter First Amount], [Enter Second Amount],
[Enter Third Amount], [Enter Fourth Amount] )
[/tt]
That will append rows but I suspect that your table will have more than just the 4 fields that you have shown and you will need to set their values as well (ID for example).

 

[nedstar1]

First, thanks very mcuh for the support. That fixed the problem.

I pasted your SQL into a new query and it does what I wanted it to do, but I'm a little confused about the remainder of your comment. The table in question exists for the sole purpose of holding these quote values so I can use them as the basis for calculations in a form.

Am I going about this completely wrong? I seem to recall reading that you could essentially create a value like this IN the query, to be used for calculations later in the same query, and that the input variable query field could be bound to a form control.

Thanks.

 

[Golom]

I didn't mean to imply that you were doing something wrong ... only that a table with four columns of numbers doesn't really relate to anything else.

I noted for example, that you have a reference to "QuoteAmounts.ID" in your previous SQL and that field is not being populated by the SQL that I provided. If you don't populate it then you don't really have any way to tell which row goes with which ID (or any ID). That means that you can't relate it to any other table because the field on which the relation would be based is missing.

Obviously however, you know your business problem better than I do so ... if it works for you ... THEN IT WORKS!