How to add records using Query

[John]

I currently have a form and subform based on two tables; tblGoodsIn and
tblGoodsInDetail. The fields in the underlying tables do not contain any
price information. only foreign key links to product and price tables. I use
combo boxes to select parts and prices. I want the form to calculate totals
as I enter the data so I can check instantly that what I am entering into
the database tallies with the supplier Invoice. .

I tried doing this using two queries as below, but I can add records in
qryGoodsInDetail, but not in qryGoodsIn. Is it because qryGoodsIn is looking
up another query (ieqryGoodsInDetail). Can I do this using queries or should
I use the form to calculate totals.

I have two queries. qryGoodsInDetail. which uses
tblGoodsInDetail
tblProductPrice
tblProductDescription
tblTaxRate

and qryGoodsIn which uses
tblCustomer
tblGoodsIn
qryGoodsInDetail.

Any help appreciated

John

[Jeff Smith]

Yes, you are right that it is the other query inside your main query that is
preventing you from adding records. Why have you got a table called
"tblProductDescription"? ProductDescription should be a field in
"tblProducts". By the looks of it you have a database that isn't normalised.
If you want to, post you table structure and relationships and we may help
guide you along the correct path.

Jeff
"John" <br****@gofree.indigo.ie> wrote in message
news:d4**********@reader01.news.esat.net...

I currently have a form and subform based on two tables; tblGoodsIn and
tblGoodsInDetail. The fields in the underlying tables do not contain any
price information. only foreign key links to product and price tables. I use combo boxes to select parts and prices. I want the form to calculate totals as I enter the data so I can check instantly that what I am entering into
the database tallies with the supplier Invoice. .

I tried doing this using two queries as below, but I can add records in
qryGoodsInDetail, but not in qryGoodsIn. Is it because qryGoodsIn is looking up another query (ieqryGoodsInDetail). Can I do this using queries or should I use the form to calculate totals.

I have two queries. qryGoodsInDetail. which uses
tblGoodsInDetail
tblProductPrice
tblProductDescription
tblTaxRate

and qryGoodsIn which uses
tblCustomer
tblGoodsIn
qryGoodsInDetail.

Any help appreciated

John

[John]

"Jeff Smith" <No****@Not.This.Address> wrote in message
news:d4**********@lust.ihug.co.nz...

Yes, you are right that it is the other query inside your main query that
is
preventing you from adding records. Why have you got a table called
"tblProductDescription"? ProductDescription should be a field in
"tblProducts". By the looks of it you have a database that isn't
normalised.
If you want to, post you table structure and relationships and we may help
guide you along the correct path.

Jeff

Sorry. That should have read tblProducts.
The structure for the relevant tables are

tblTaxRate
pkTaxRateID-------[ tblGoodsInDetail
TaxRate [ pkGoodsInDetailID
TaxDate [ Quantity
[---------fkTaxRateID
tblGoodsIn
tblProductPrices fkGoodsInID-------------pkGoodsInID
pkProductPriceID-------------fkProductPriceID DeliveryDate
UnitPrice
SupplierInvoiceNumber
UnitPriceDate
fkCompanyName
Markup tblProducts
fkPaymentID
fkProductID--------------pkProductID
ProductCode
ProductDescription
QtyPerUnit
tblProductCategory UnitsInStock
pkCategoryID------------f kCategoryID
ProductCategory

There are other tables, but these are the relevant ones.
John

[John]

That didn't display right. Here are the relevant tables. The primary and
foreign keys are self explanatory.

tblTaxRate
pkTaxRateID
TaxRate
TaxDate

tblGoodsInDetail
pkGoodsInDetailID
Quantity
fkGoodsInID
fkTaxRateID
fkProductPriceID

tblGoodsIn
pkGoodsInID
DeliveryDate
SupplierInvoiceNumber
fkCustomerID
fkPaymentID

tblCustomers
pkCustomerID
CustomerName
fkAddressID

tblProductPrices
pkProductPriceID
UnitPrice
UnitPriceDate
Markup
fkProductID

tblProducts
pkProductID
ProductCode
ProductDescription
QtyPerUnit
UnitsInStock
f kCategoryID

tblProductCategory
pkCategoryID
ProductCategory

There are other tables, but these are the relevant ones.

John

[Jeff Smith]

The table structure looks fine but in tblGoodsInDetail the ProductPriceID
should be stored to capture the price the product was paid for. Having this
linked at table level can cause problems later if someone changes the price.
i.e. Paid $3.50 for a widget on 23 Mar 2002 and then someone decides to
change the price for a widget in the tblProductPrices to $3.75 instead of
adding a new price and date as with your table structure. This will upset
the history because all calculations will show $3.75 although $3.50 was the
price paid for that widget. Storing the price at the time of the transaction
also makes it easier when creating queries. Fewer tables are required in
your query resulting in a query that will more likely to work as you like it
to and be more updatable.

Back to your original problem. There's no need to use a query for your main
form, just use the tblGoodsIn. Use a query for your sub-form based on the
tblGoodsInDetail. To display the total price of the order in the main form,
add a text box to the Form Footer of the sub-form and in its Control Source
type =Sum([ExtendedPrice]) where the [ExtendedPrice] is the name of a
calculated field in the qryGoodInDetail query
ExtendedPrice:(Price*Quantity)+((Price*Quantity)*T axRate). In the main form,
create another unbound text box and in its Control Source reference the text
box control, = [YourSubFormName].[Form]![YourTextBoxName].

Have a look at the Orders form in the sample Northwind database that comes
with Access to help you.

Jeff
"John" <br****@gofree.indigo.ie> wrote in message
news:d4**********@reader01.news.esat.net...

That didn't display right. Here are the relevant tables. The primary and
foreign keys are self explanatory.

tblTaxRate
pkTaxRateID
TaxRate
TaxDate

tblGoodsInDetail
pkGoodsInDetailID
Quantity
fkGoodsInID
fkTaxRateID
fkProductPriceID

tblGoodsIn
pkGoodsInID
DeliveryDate
SupplierInvoiceNumber
fkCustomerID
fkPaymentID

tblCustomers
pkCustomerID
CustomerName
fkAddressID

tblProductPrices
pkProductPriceID
UnitPrice
UnitPriceDate
Markup
fkProductID

tblProducts
pkProductID
ProductCode
ProductDescription
QtyPerUnit
UnitsInStock
f kCategoryID

tblProductCategory
pkCategoryID
ProductCategory

There are other tables, but these are the relevant ones.

John

[isnotreallyme]

On Mon, 25 Apr 2005 20:53:18 +1200, "Jeff Smith"
<No****@Not.This.Address> wrote:

The table structure looks fine but in tblGoodsInDetail

While tblGoodSin has mild interest, tblGoodSinDetail might have
significant commercial value. Perhaps posting a few would be good
reading?

John

[John]

Never saw it that way, me thinks you have one track mind :).
"isnotreallyme" <is***********@isnotreallyme.com> wrote in message
news:42****************@news.INDIVIDUAL.NET...

On Mon, 25 Apr 2005 20:53:18 +1200, "Jeff Smith"
<No****@Not.This.Address> wrote:

The table structure looks fine but in tblGoodsInDetail

While tblGoodSin has mild interest, tblGoodSinDetail might have
significant commercial value. Perhaps posting a few would be good
reading?

John

[John]

"Jeff Smith" <No****@Not.This.Address> wrote in message
news:d4**********@lust.ihug.co.nz...

The table structure looks fine but in tblGoodsInDetail the ProductPriceID
should be stored to capture the price the product was paid for. Having
this
linked at table level can cause problems later if someone changes the
price.
i.e. Paid $3.50 for a widget on 23 Mar 2002 and then someone decides to
change the price for a widget in the tblProductPrices to $3.75 instead of
adding a new price and date as with your table structure. This will upset
the history because all calculations will show $3.75 although $3.50 was
the
price paid for that widget. Storing the price at the time of the
transaction
also makes it easier when creating queries. Fewer tables are required in
your query resulting in a query that will more likely to work as you like
it
to and be more updatable.

Back to your original problem. There's no need to use a query for your
main
form, just use the tblGoodsIn. Use a query for your sub-form based on the
tblGoodsInDetail. To display the total price of the order in the main
form,
add a text box to the Form Footer of the sub-form and in its Control
Source
type =Sum([ExtendedPrice]) where the [ExtendedPrice] is the name of a
calculated field in the qryGoodInDetail query
ExtendedPrice:(Price*Quantity)+((Price*Quantity)*T axRate). In the main
form,
create another unbound text box and in its Control Source reference the
text
box control, = [YourSubFormName].[Form]![YourTextBoxName].

Have a look at the Orders form in the sample Northwind database that comes
with Access to help you.

Thanks Jeff
Your suggestion on the prices makes sense so I'll implement it. But doesn't
that de-normalize the database. I thought the idea was not to duplicate any
data.

Regards

John