How to add records using Query

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

re: How to add records using Query

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" <bravo6@gofree.indigo.ie> wrote in message
news:d4gik2$te5$1@reader01.news.esat.net...[color=blue]
> 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[/color]
use[color=blue]
> combo boxes to select parts and prices. I want the form to calculate[/color]
totals[color=blue]
> 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[/color]
looking[color=blue]
> up another query (ieqryGoodsInDetail). Can I do this using queries or[/color]
should[color=blue]
> 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
>
>[/color]

re: How to add records using Query

"Jeff Smith" <NoSpam@Not.This.Address> wrote in message
news:d4h05d$7h9$1@lust.ihug.co.nz...[color=blue]
> 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
>[/color]
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

re: How to add records using Query

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.[color=blue]
>
>
> John
>[/color]

re: How to add records using Query

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" <bravo6@gofree.indigo.ie> wrote in message
news:d4h7uf$4u5$1@reader01.news.esat.net...[color=blue]
> 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.[color=green]
> >
> >
> > John
> >[/color]
>
>[/color]

re: How to add records using Query

On Mon, 25 Apr 2005 20:53:18 +1200, "Jeff Smith"
<NoSpam@Not.This.Address> wrote:
[color=blue]
>The table structure looks fine but in tblGoodsInDetail[/color]

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

John

re: How to add records using Query

Never saw it that way, me thinks you have one track mind :).


"isnotreallyme" <isnotreallyme@isnotreallyme.com> wrote in message
news:426cfd24.199104847@news.INDIVIDUAL.NET...[color=blue]
> On Mon, 25 Apr 2005 20:53:18 +1200, "Jeff Smith"
> <NoSpam@Not.This.Address> wrote:
>[color=green]
>>The table structure looks fine but in tblGoodsInDetail[/color]
>
> While tblGoodSin has mild interest, tblGoodSinDetail might have
> significant commercial value. Perhaps posting a few would be good
> reading?
>
> John
>[/color]

re: How to add records using Query

"Jeff Smith" <NoSpam@Not.This.Address> wrote in message
news:d4ib5o$tsb$1@lust.ihug.co.nz...[color=blue]
> 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.[/color]



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