How do I copy a record from one query to another?

[Frank Martin]

I have to create a Debtor account in my general ledger.

Therefore my aim is to copy some fields of a record from
the query "QryInvoiceTotals" into another query
"QryLedgerTxns".

The fields in each query are of the same type, but with
slightly different names.

The queries are quite separate in the database.

Can someone give me a start? I have tried a "SetValue"
macro with no success.

Please help, Frank

 

[Dale Fye]

Frank,

You cannot copy a record from a query to another query (period).

You can however copy a record from a query to a table, which is returned by
another query.

The syntax for this would be something like:

INSERT INTO tblDestination (field1, field2, field3)
SELECT field1, field2, field3
FROM qryInvoiceTotals

Exactly what fields you need to populate in the destination table will
depend on the table, and if you don't set the values appropriately, it still
might not show up in qryLedgerTxns.

 

[John W. Vinson]

Therefore my aim is to copy some fields of a record from
the query "QryInvoiceTotals" into another query
"QryLedgerTxns".

Data is not stored in queries. Data is stored in tables, and ONLY in tables. A
Query is just a view of a table, a way of selectively retrieving certain
records and fields from the table and arranging them as desired. Changing or
inserting data into a Query changes or inserts data into the underlying Table
(if the query is updateable).

What tables underlie these queries? How are the tables (not the queries)
related, if at all?

 

[Frank Martin]

Thanks; I figured out that I need an append query for this
task and I managed to make one.

The aim was to append records from "QryINVOICEtotals" (a sum
query to add the invoice lines totals for an invoice) to
"tblLedgerTxns".

I used a copy of this query to make the append one.

I had to make some adjustments to the "QryINVOICEtotals" by
introducing a new field called "LedgerAccount" which is a
number designating which ledger account the Debtors are
inserted, and this is number 7. I had therefore to fill
this field with 7 for 4500records though I used the "find &
replace" method for this (find "null" and replace with "7".)
The relevant field in the "QryINVOICEtotals" is
automatically filled in with a "7" from a related table
"tblCustOrders" for with the field default value is set at
"7".

The appending only worked after I deleted a primary-key
field (unrequired) from the design grid of
"QryINVOICEtotals".

My next task is to append the "tblLedgerTxns" one record at
a time by using this append query, but I need some form of
prompt to appear when an invoice is completed, asking "Post
to Ledger? Y/N")

How should I start with this task; should I use a macro?

Thank you for the help, Frank

 

[Frank Martin]

in message

Data is not stored in queries. Data is stored in tables,
and ONLY in tables. A
Query is just a view of a table, a way of selectively
retrieving certain
records and fields from the table and arranging them as
desired. Changing or
inserting data into a Query changes or inserts data into
the underlying Table
(if the query is updateable).

What tables underlie these queries? How are the tables
(not the queries)
related, if at all?

Thanks. The ledger transactions are based on one table only
"tblLedgerTxns", though in the relationship screen I have
connected it to two other tables, and the three are
"tblLedgerAccntType" has many "tblLedgerAccnts" has many
tblLedgerTxns." These three connected tables are alone on
the relationship screen.


The "QryINVOICEtotals" is a summary query with a source
"QueryINVOICE." It produces a list of invoices which
themselves are a collection of invoice lines.

The "QryINVOICE" is very detailed and is a joined group of
tables "tblPeople", tblAddressType", "tblPeopleType",
"tblAddressDetail", "tblCustOrders", "tblInvoiceLines",
"tblProducts".

My database has evolved over a long time. But now my
accountants want me to improve the ledger.

Regard,Frank