Table not updating

K

kev.thorpe

Help! I am getting very frustrated and I hope someone can offer some
guidance.

I have the following 3 tables

#1 - tblGLInvoice (no PK) #2 - tbl InvoiceTracking #3-
tblProjectInfo
Invoice ID InvoiceID
(PK) ProjectID (PK)
GL#
ProjectID Project#
Amount
Project# FN#

FN# ProjectName

GL# AgencyID

SupplierID ProjectDescription

FundingID

InvNumber ProjectStart

Date ProjectEnd

InvDescription Originator

InvSubtotal PMID

GST ProjectBudget

PlannerID ActualFinance

FinanceDAte ContractorID

ConsultantID

StatusID

PhaseID

LienStatus

LienStatusComments

Notes

The relationships are as follows:
tblInvoiceTracking / tblGLInvoice - 1 to many via InvoiceID
tblProjectInfo / tblInvoiceTracking - 1 to many via ProjectID (I have
had trouble enforcing referential integrity with this table)

I have created a query based on two tables -tblSupplierId and
tblInvoiceTracking (1 to many, connected by SupplierID)

I have also created a form based on tblInvoiceTracking with a subform
based on tblGLInvoice (parent child relationship based on InvoiceID)

The problem is that when I enter invoice information using the form,
the tblInvoiceTracking is only being partially updated - only a $0 is
showing up for invSubTotal field. The query is also not updating
properly, again the invSubTotal field is not updating. I use the query
to store calculations based on the amount in invSubTotal to appear on
reports and forms.

I am racking my brains trying to figure out what the heck to do
next!! Any input greatly appreciated.

So sorry for all the info....too much?

Kevin
 
K

kev.thorpe

Help! I am getting very frustrated and I hope someone can offer some
guidance.

I have the following 3 tables

#1 - tblGLInvoice (no PK) #2 - tbl InvoiceTracking #3-
tblProjectInfo
Invoice ID InvoiceID
(PK) ProjectID (PK)
GL#
ProjectID Project#
Amount
Project# FN#

FN# ProjectName

GL# AgencyID

SupplierID ProjectDescription

FundingID

InvNumber ProjectStart

Date ProjectEnd

InvDescription Originator

InvSubtotal PMID

GST ProjectBudget

PlannerID ActualFinance

FinanceDAte ContractorID

ConsultantID

StatusID

PhaseID

LienStatus

LienStatusComments

Notes

The relationships are as follows:
tblInvoiceTracking / tblGLInvoice - 1 to many via InvoiceID
tblProjectInfo / tblInvoiceTracking - 1 to many via ProjectID (I have
had trouble enforcing referential integrity with this table)

I have created a query based on two tables -tblSupplierId and
tblInvoiceTracking (1 to many, connected by SupplierID)

I have also created a form based on tblInvoiceTracking with a subform
based on tblGLInvoice (parent child relationship based on InvoiceID)

The problem is that when I enter invoice information using the form,
the tblInvoiceTracking is only being partially updated - only a $0 is
showing up for invSubTotal field. The query is also not updating
properly, again the invSubTotal field is not updating. I use the query
to store calculations based on the amount in invSubTotal to appear on
reports and forms.

I am racking my brains trying to figure out what the heck to do
next!! Any input greatly appreciated.

So sorry for all the info....too much?

Kevin
Click to expand...

Okay - the tables were lined up nicely when I sent it - I will rewrite
the tables here;

tblGLInvoice (no PK)
InvoiceID
GL#
Amount

tblInvoiceTracking
InvoiceID (PK)
ProjectID
Project#
FN#
GL#
SupplierID
ConsultantName
InvNumber
Date
InvDescription
InvSubtotal
GST
PlannerId
FinanceDate

tblProjectInfo:
ProjectID (PK)
Project#
FN#
ProjectName
AgencyID
ProjectDescription
FundingID
and a bunch more unrelated to the others

Sorry about that - hope this helps

Kevin
 
J

John W. Vinson

The relationships are as follows:
tblInvoiceTracking / tblGLInvoice - 1 to many via InvoiceID
tblProjectInfo / tblInvoiceTracking - 1 to many via ProjectID (I have
had trouble enforcing referential integrity with this table)
Click to expand...

What sort of "trouble"? Does each invoicetracking record in fact refer
to one and only one project?
I have created a query based on two tables -tblSupplierId and
tblInvoiceTracking (1 to many, connected by SupplierID)

I have also created a form based on tblInvoiceTracking with a subform
based on tblGLInvoice (parent child relationship based on InvoiceID)

The problem is that when I enter invoice information using the form,
the tblInvoiceTracking is only being partially updated - only a $0 is
showing up for invSubTotal field. The query is also not updating
properly, again the invSubTotal field is not updating. I use the query
to store calculations based on the amount in invSubTotal to appear on
reports and forms.
Click to expand...

You should NOT be storing totals in your table... except in very
unusual circumstances. A calculated field on a Form, or in a Select
Query, will NOT store the calculated result anywhere.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


John W. Vinson [MVP]
 
K

kev.thorpe

Hi John,


Thank you for you reply:)

I am not attempting to store calculated totals...when I first created
the database that is what I did. Now I have gone back and removed
all the calculated totals from tables and only have them in queries
and forms. The total that is not updating is the invoice subtotal
which is not a calculated total. It is the amount that is used as a
base for all further calculations (ie tax, tax rebate, total etc). It
is simply the data amount that I am entering in my subform that is not
updating my query or table. Everything looks fine on the invoice
tracking form which is great, but as I said I need that subtotal in my
query and table to build other forms and reports.

"What sort of "trouble"? Does each invoicetracking record in fact
refer
to one and only one project?" Each invoicetracking record does in
fact refer to one and only one project(but sometimes several GL
accounts - hence the subform) The trouble I am having is an error
message when I try to enforce referential integrity that says
something (I"m not at work so I can't replicate it right now!) to do
with there not being records in my primary table for all the fields in
my invoice tracking table. Like I said I can't completely recollect.

Any insight very much appreciated. Thanks again

Kevin
 
K

kev.thorpe

Hi again,

I made an error in my last post - subtotal didn't use to be a
calculated field! It is now, the reason I had to make it a calculated
field was in my original setup if I had a single invoice, for a single
project, but with separate amounts being charged to different GL's I
used to have to split the invoice and enter it as two separate
invoices - there was no way to charge 2 or 3 gl's on one invoice. I
do think in my database now I need to store that subtotal (or if not
the individual gl amounts and then I can subtotal on my new form or
query?). I guess this is the crux of my problem.

kevin
 
Top