Setting up credit notes

[Franc Sutherland]

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.

 

[John Nurick]

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

 

[Franc Sutherland]

Hi John,

That looks good. An earlier respondee came up with a similar Insert Into
SQL statement solution for this question. I'll be trying this out for sure.

Many thanks,

Franc.

 

[Franc Sutherland]

Hi John,

I'm nearly there. I don't understand what I should be substituting in for
the '2345' in the SELECT '2345' AS statement below?

Thanks,

Franc.

 

[John Nurick]

Hi Franc,

You must have a field in your CreditNoteLines table that links each
line item to the 'parent' record in the CreditNotes table. Presumably
by the time you're ready to create the CreditNoteLines records you
have created a record in CreditNotes for the credit note that the user
is creating; you need the primary key value of that record.