Invoicing problem

[Joan]

Hi,

How does one reference other records in the same table from a form and
insert the form's present record's primary key value in a foreign key field
of the other records? If this sounds confusing, I'll try to explain what I
am trying to do as best as I can. Please bear with me.

My invoice form lets the user enter invoice information that is recorded in
the Invoices table. Both invoices and adjustments are recorded to the
Invoices table( a more descriptive name would be Transactions table), the
invoices via the invoice form and the adjustments via either a credit memo
or a debit memo. Both invoice and adjustment records have different Invoice
Numbers as it's primary key. Below is the table structure of the Invoices
table:

Table: Invoices
[Invoice Number] Primary Key
[Type] Type of transaction: "INV" for Invoice, "DB" for Debit,
"CR" for Credit, or "ADJ" for Adjustment
[Store] The store code of the Store(or customer) the transaction
is applied to.
[DateSold] Date of the transaction
[Debit] Amount debited
[Credit] Amount credited
[Dog Number] The [Dog Number] of the dog (product) that the
adjustment applies to
[Comment] Explanation of the adjustment, debit or credit
[OnInvoice] Refers to the Invoice Number of the invoice that
the adjustment is applied to.

The last three fields only apply to adjustment transactions, not invoice
transactions.


Also I have a report, called rptInvoiceStatement , that is printed and sent
out every week to any customers(stores) that have a balance due or credit .
The invoice statement lists the dogs recorded in the Invoice Form and
adjustments if there are any that have occurred since last week's invoice
statements were sent out. The Invoice form is filled out just prior to
printing the invoice statement. My problem is in matching the adjustments to
the correct invoice when the invoice statements are printed. The foreign
key, [OnInvoice] refers to the Invoice Number that the adjustment should
apply to. In other words, the foreign key references the same table that it
is in.

Since the invoice that any adjustments apply to is created after the
adjustments, the [OnInvoice] field of Adjustment records is unknown until
the invoice is created. I was thinking that the best time to run an update
query would be at the end of the Invoice Form where somehow I put the
Invoice Number value from the Invoice Form into the [OnInvoice] fields of
the adjustment records. Would someone be able to tell me the best way to
accomplish this? I am having problems pulling up the right adjustment
records in my update query. I want to query for the Store that is on the
Invoice Form and for any adjustment dates for that store in the last week,
Forms![Invoice Form]![DateSold] - 7. So then, are Forms![Invoice
Form]![Store] and Forms![Invoice Form]![DateSold] parameters for my query?
Am I approaching this problem right?

Joan

 

[Gary Miller]

Joan,

What you are asking for is a very standard usage and native
capability of Access.

You know those fields that are kind of stumping you? You are
right. You need to create a separate table just to a couple
of items. Call this table tblTransactions and make an
AutoNumber field to be TransactionID. Make this the primary
key of the table.

This will be a child table to all of the transactions that
occur to the Parent Invoice. You can have as many
transactions as you desire.

The functionality of this will happen by creating a Parent
Form (Invoice) with a Child SubForm that will display all of
the Invoice Transactions and they will all be tied together
simply by the InvoiceID.

Later on when you want reporting, you may end up tying the
two ( and maybe more ) tables together with a query.

Look up subforms in Help and look at the sample Northwinds
database included with Access.

Gary Miller
Sisters, OR

Hi,

How does one reference other records in the same table from a form and
insert the form's present record's primary key value in a foreign key field
of the other records? If this sounds confusing, I'll try to explain what I
am trying to do as best as I can. Please bear with me.

My invoice form lets the user enter invoice information that is recorded in
the Invoices table. Both invoices and adjustments are recorded to the
Invoices table( a more descriptive name would be Transactions table), the
invoices via the invoice form and the adjustments via either a credit memo
or a debit memo. Both invoice and adjustment records have different Invoice
Numbers as it's primary key. Below is the table structure of the Invoices
table:

Table: Invoices
[Invoice Number] Primary Key
[Type] Type of transaction: "INV" for Invoice, "DB" for Debit,
"CR" for Credit, or "ADJ" for Adjustment
[Store] The store code of the Store(or customer) the transaction
is applied to.
[DateSold] Date of the transaction
[Debit] Amount debited
[Credit] Amount credited
[Dog Number] The [Dog Number] of the dog (product) that the
adjustment applies to
[Comment] Explanation of the adjustment, debit or credit
[OnInvoice] Refers to the Invoice Number of the invoice that
the adjustment is applied to.

The last three fields only apply to adjustment transactions, not invoice
transactions.


Also I have a report, called rptInvoiceStatement , that is printed and sent
out every week to any customers(stores) that have a balance due or credit .
The invoice statement lists the dogs recorded in the Invoice Form and
adjustments if there are any that have occurred since last week's invoice
statements were sent out. The Invoice form is filled out just prior to
printing the invoice statement. My problem is in matching the adjustments to
the correct invoice when the invoice statements are printed. The foreign
key, [OnInvoice] refers to the Invoice Number that the adjustment should
apply to. In other words, the foreign key references the same table that it
is in.

Since the invoice that any adjustments apply to is created after the
adjustments, the [OnInvoice] field of Adjustment records is unknown until
the invoice is created. I was thinking that the best time to run an update
query would be at the end of the Invoice Form where somehow I put the
Invoice Number value from the Invoice Form into the [OnInvoice] fields of
the adjustment records. Would someone be able to tell me the best way to
accomplish this? I am having problems pulling up the right adjustment
records in my update query. I want to query for the Store that is on the
Invoice Form and for any adjustment dates for that store in the last week,
Forms![Invoice Form]![DateSold] - 7. So then, are Forms![Invoice
Form]![Store] and Forms![Invoice Form]![DateSold] parameters for my query?
Am I approaching this problem right?

Joan