473,842 Members | 1,893 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to add records using Query

I currently have a form and subform based on two tables; tblGoodsIn and
tblGoodsInDetai l. 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
qryGoodsInDetai l, but not in qryGoodsIn. Is it because qryGoodsIn is looking
up another query (ieqryGoodsInDe tail). Can I do this using queries or should
I use the form to calculate totals.

I have two queries. qryGoodsInDetai l. which uses
tblGoodsInDetai l
tblProductPrice
tblProductDescr iption
tblTaxRate

and qryGoodsIn which uses
tblCustomer
tblGoodsIn
qryGoodsInDetai l.

Any help appreciated

John
Nov 13 '05 #1
7 9272
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
"tblProductDesc ription"? ProductDescript ion should be a field in
"tblProduct s". 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.new s.esat.net...
I currently have a form and subform based on two tables; tblGoodsIn and
tblGoodsInDetai l. 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
qryGoodsInDetai l, but not in qryGoodsIn. Is it because qryGoodsIn is looking up another query (ieqryGoodsInDe tail). Can I do this using queries or should I use the form to calculate totals.

I have two queries. qryGoodsInDetai l. which uses
tblGoodsInDetai l
tblProductPrice
tblProductDescr iption
tblTaxRate

and qryGoodsIn which uses
tblCustomer
tblGoodsIn
qryGoodsInDetai l.

Any help appreciated

John

Nov 13 '05 #2

"Jeff Smith" <No****@Not.Thi s.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
"tblProductDesc ription"? ProductDescript ion should be a field in
"tblProduct s". 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-------[ tblGoodsInDetai l
TaxRate [ pkGoodsInDetail ID
TaxDate [ Quantity
[---------fkTaxRateID
tblGoodsIn
tblProductPrice s fkGoodsInID-------------pkGoodsInID
pkProductPriceI D-------------fkProductPriceI D DeliveryDate
UnitPrice
SupplierInvoice Number
UnitPriceDate
fkCompanyName
Markup tblProducts
fkPaymentID
fkProductID--------------pkProductID
ProductCode
ProductDescript ion
QtyPerUnit
tblProductCateg ory UnitsInStock
pkCategoryID------------f kCategoryID
ProductCategory

There are other tables, but these are the relevant ones.
John
Nov 13 '05 #3
That didn't display right. Here are the relevant tables. The primary and
foreign keys are self explanatory.

tblTaxRate
pkTaxRateID
TaxRate
TaxDate

tblGoodsInDetai l
pkGoodsInDetail ID
Quantity
fkGoodsInID
fkTaxRateID
fkProductPriceI D

tblGoodsIn
pkGoodsInID
DeliveryDate
SupplierInvoice Number
fkCustomerID
fkPaymentID

tblCustomers
pkCustomerID
CustomerName
fkAddressID

tblProductPrice s
pkProductPriceI D
UnitPrice
UnitPriceDate
Markup
fkProductID

tblProducts
pkProductID
ProductCode
ProductDescript ion
QtyPerUnit
UnitsInStock
f kCategoryID

tblProductCateg ory
pkCategoryID
ProductCategory

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


John

Nov 13 '05 #4
The table structure looks fine but in tblGoodsInDetai l 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 tblProductPrice s 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
tblGoodsInDetai l. 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*Quanti ty)*TaxRate). 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.new s.esat.net...
That didn't display right. Here are the relevant tables. The primary and
foreign keys are self explanatory.

tblTaxRate
pkTaxRateID
TaxRate
TaxDate

tblGoodsInDetai l
pkGoodsInDetail ID
Quantity
fkGoodsInID
fkTaxRateID
fkProductPriceI D

tblGoodsIn
pkGoodsInID
DeliveryDate
SupplierInvoice Number
fkCustomerID
fkPaymentID

tblCustomers
pkCustomerID
CustomerName
fkAddressID

tblProductPrice s
pkProductPriceI D
UnitPrice
UnitPriceDate
Markup
fkProductID

tblProducts
pkProductID
ProductCode
ProductDescript ion
QtyPerUnit
UnitsInStock
f kCategoryID

tblProductCateg ory
pkCategoryID
ProductCategory

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


John


Nov 13 '05 #5
On Mon, 25 Apr 2005 20:53:18 +1200, "Jeff Smith"
<No****@Not.Thi s.Address> wrote:
The table structure looks fine but in tblGoodsInDetai l


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

John

Nov 13 '05 #6
Never saw it that way, me thinks you have one track mind :).
"isnotreall yme" <is***********@ isnotreallyme.c om> wrote in message
news:42******** ********@news.I NDIVIDUAL.NET.. .
On Mon, 25 Apr 2005 20:53:18 +1200, "Jeff Smith"
<No****@Not.Thi s.Address> wrote:
The table structure looks fine but in tblGoodsInDetai l


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

John

Nov 13 '05 #7
"Jeff Smith" <No****@Not.Thi s.Address> wrote in message
news:d4******** **@lust.ihug.co .nz...
The table structure looks fine but in tblGoodsInDetai l 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 tblProductPrice s 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
tblGoodsInDetai l. 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*Quanti ty)*TaxRate). 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
Nov 13 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
1721
by: jsfromynr | last post by:
Hi all, Here is the table and DML statments CREATE TABLE ( , NULL , DEFAULT (1), -- 1 Pending ,2 Approved (20) )
1
4990
by: PMBragg | last post by:
ORINGINAL Post >Thank you in advance. I'm trying to pull all inventory items from December >of the previous year back to 4 years for my accountant. I know this can be >done, but I'm drawing a blank. I've tried; > >DateDiff("y",-4,DateIn) and get errors > >Please any assistance would be greatly appreciated. >
2
4996
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since records to be imported can have duplicate records of the composite key I need to clean all but one of the...
6
8169
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join" in another table but I have not found what the field criteria should look like? 2: And if/when I succeed I should further like to build a new record (with
4
2287
by: Brian | last post by:
I hope this will make sense. I'm trying to filter the records in a table based on records in a 2nd table. The trick is, I can't use a query. I'm trying to filter down the number of records going to a SmartList to go db. The SMtG Access sync will not allow you to sync to a query based on more than one table. And if I sync all the records in table 1 (6000)and table 2 (15,000), it take a loooooong time to sync I've got an easy solution...
13
3490
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the query: QryICTMassDistribution3) , I then use a form and the code below to create a new record in the corrispondence table to show what corrispondence has been sent to various companies.
8
2812
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table, I have a where clause which states : where PermitID like @WorkType order by WorkStart DESC
11
3692
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night) and the 'employee name'. There is another table which assigns an ID to the Shifts, i.e. 1,2 and 3 for morn, eve & night shifts respectively. From the mother table, the incentive is calculated datewise for each employee as per his shift duty. In...
6
9435
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate report" button located on my form to print all of the list box values (that have been updated via selection from combo boxes) from the form to the report. I've tried using a macro with the code: Macro Name: cmdGenerateReport : On Click Action:...
2
4456
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for statistical purposes. I've been using Here's the situation: I have two main tables:
0
9865
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9709
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10669
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10303
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9448
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7025
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.