If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I copy a record from one query to another?
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 |
#2
|
|||
|
|||
How do I copy a record from one query to another?
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. ---- HTH Dale "Frank Martin" wrote: 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 |
#3
|
|||
|
|||
How do I copy a record from one query to another?
On Wed, 23 Sep 2009 17:40:57 +1000, "Frank Martin" wrote:
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? -- John W. Vinson [MVP] |
#4
|
|||
|
|||
How do I copy a record from one query to another?
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 "Dale Fye" wrote in message ... 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. ---- HTH Dale "Frank Martin" wrote: 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 |
#5
|
|||
|
|||
How do I copy a record from one query to another?
"John W. Vinson" wrote in message ... On Wed, 23 Sep 2009 17:40:57 +1000, "Frank Martin" wrote: 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? -- John W. Vinson [MVP] 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 |
Thread Tools | |
Display Modes | |
|
|