Hello everyone,

I received the response below for a previous question. I understand the
logic behind the reply, and I am sure it will work. I can't, however, figure
out what I should be substituting for '2345' in the "SELECT '2345' AS" part
of the solution below?

I'm getting very close to running out of time and would greatly appreciate
any assistance.

Regards,

Franc.

Hi Franc,
>
> Use an append query. Probably you'll need to write VBA code to
> assemble and execute the necessary SQL statement, which could look
> something like this:
>
> INSERT INTO CreditNoteLines (
> CreditNoteID, SalesOrderID, PartNumber, Description,
> Quantity, UnitPrice, Tax)
> SELECT '2345' AS CreditNoteID,
> SalesOrderID, PartNumber, Description,
> Quantity, UnitPrice, Tax)
> FROM SalesOrderLines
> WHERE SalesOrderID = '9876'
> ;
>
>
>
>
> On Tue, 31 Jul 2007 08:42:05 -0700, Franc Sutherland
> <(E-Mail Removed)> wrote:
>
> >Hi,
> >
> >I'm making a sales and invoicing system, but I'm struggling with the credit
> >notes.
> >
> >So far, I have set up the sales orders and sales order lines in two separate
> >tables.
> >
> >Same with the credit notes, and credit note lines.
> >
> >The credit note is based on a sales order, but doesn't necessarily contain
> >all the same lines as there were in the sales order. Therefore, I need to
> >pull through a copy of the sales order lines into the credit note lines, and
> >then edit from there.
> >
> >How do I pull a copy of the sales lines for a particular sales order through
> >into the relevant credit note lines table associated with the original sales
> >order?
> >
> >Thanks,
> >
> >Franc.

Hi!

If I understand what you are trying to do (selecting data from SalesOrders
and inserting the selected data to CreditNotes), then I think you should:

INSERT INTO CreditNoteLines (
CreditNoteID, SalesOrderID, PartNumber, Description, Quantity, UnitPrice, Tax)
SELECT CreditNoteID,SalesOrderID, PartNumber, Description,Quantity,
UnitPrice, Tax
FROM SalesOrderLines
WHERE SalesOrderID = '9876';

Hope this helps!

- Beginner -



"Franc Sutherland" wrote:

> Hello everyone,
>
> I received the response below for a previous question. I understand the
> logic behind the reply, and I am sure it will work. I can't, however, figure
> out what I should be substituting for '2345' in the "SELECT '2345' AS" part
> of the solution below?
>
> I'm getting very close to running out of time and would greatly appreciate
> any assistance.
>
> Regards,
>
> Franc.
>
> Hi Franc,
> >
> > Use an append query. Probably you'll need to write VBA code to
> > assemble and execute the necessary SQL statement, which could look
> > something like this:
> >
> > INSERT INTO CreditNoteLines (
> > CreditNoteID, SalesOrderID, PartNumber, Description,
> > Quantity, UnitPrice, Tax)
> > SELECT '2345' AS CreditNoteID,
> > SalesOrderID, PartNumber, Description,
> > Quantity, UnitPrice, Tax)
> > FROM SalesOrderLines
> > WHERE SalesOrderID = '9876'
> > ;
> >
> >
> >
> >
> > On Tue, 31 Jul 2007 08:42:05 -0700, Franc Sutherland
> > <(E-Mail Removed)> wrote:
> >
> > >Hi,
> > >
> > >I'm making a sales and invoicing system, but I'm struggling with the credit
> > >notes.
> > >
> > >So far, I have set up the sales orders and sales order lines in two separate
> > >tables.
> > >
> > >Same with the credit notes, and credit note lines.
> > >
> > >The credit note is based on a sales order, but doesn't necessarily contain
> > >all the same lines as there were in the sales order. Therefore, I need to
> > >pull through a copy of the sales order lines into the credit note lines, and
> > >then edit from there.
> > >
> > >How do I pull a copy of the sales lines for a particular sales order through
> > >into the relevant credit note lines table associated with the original sales
> > >order?
> > >
> > >Thanks,
> > >
> > >Franc.

'Beginner' is probably right. The way you have it, with the '2345', will
cause the first field to always have a value of '2345'. I'm assuming you
really want that field to have the value of the CreditNoteID field, whcih is
what you'll get with that Beginner showed you. I think you only want to use
the 'AS' clause if you want a constant as a value (like you had with the
'2345'), or if you are calculating a value (e.g. Field1/Field2), or if you
want to rename a field.

"Franc Sutherland" wrote:

> Hello everyone,
>
> I received the response below for a previous question. I understand the
> logic behind the reply, and I am sure it will work. I can't, however, figure
> out what I should be substituting for '2345' in the "SELECT '2345' AS" part
> of the solution below?
>
> I'm getting very close to running out of time and would greatly appreciate
> any assistance.
>
> Regards,
>
> Franc.
>
> Hi Franc,
> >
> > Use an append query. Probably you'll need to write VBA code to
> > assemble and execute the necessary SQL statement, which could look
> > something like this:
> >
> > INSERT INTO CreditNoteLines (
> > CreditNoteID, SalesOrderID, PartNumber, Description,
> > Quantity, UnitPrice, Tax)
> > SELECT '2345' AS CreditNoteID,
> > SalesOrderID, PartNumber, Description,
> > Quantity, UnitPrice, Tax)
> > FROM SalesOrderLines
> > WHERE SalesOrderID = '9876'
> > ;
> >
> >
> >
> >
> > On Tue, 31 Jul 2007 08:42:05 -0700, Franc Sutherland
> > <(E-Mail Removed)> wrote:
> >
> > >Hi,
> > >
> > >I'm making a sales and invoicing system, but I'm struggling with the credit
> > >notes.
> > >
> > >So far, I have set up the sales orders and sales order lines in two separate
> > >tables.
> > >
> > >Same with the credit notes, and credit note lines.
> > >
> > >The credit note is based on a sales order, but doesn't necessarily contain
> > >all the same lines as there were in the sales order. Therefore, I need to
> > >pull through a copy of the sales order lines into the credit note lines, and
> > >then edit from there.
> > >
> > >How do I pull a copy of the sales lines for a particular sales order through
> > >into the relevant credit note lines table associated with the original sales
> > >order?
> > >
> > >Thanks,
> > >
> > >Franc.