A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Add table to query



 
 
Thread Tools Display Modes
  #21  
Old December 6th, 2008, 12:21 AM posted to microsoft.public.access.tablesdbdesign
cjgav
external usenet poster
 
Posts: 54
Default Add table to query

thank you for your help

"Steve" wrote:

Just an idea .............

Make labour fee a "part" in your part table. If you have more than one
labour fee depending on the the type, duration or whatever of the labour,
make several labour records in your part table. Then treat labour as one or
more line items on your invoice the same as you treat one or more parts.

TblPart
PartID Partname PartPrice
1 PartA $3.50
2 PartB $.75
3 PartC $8.90
4 Labour Type A $25 (per hour)
5 Labour Type B $55 (per hour)

Steve




"cjgav" wrote in message
...
My problem is my invoices sometimes only have a labour fee and no parts
(products) are used this expression in the query creates a null error in
my
report,
ExtendedPrice: CCur([Products].[UnitPrice]
*[Quantity]*(1-[Discount])/100)*100

Any idea how I could prevent this



"Steve" wrote:

Your original query returned all the records that met the criteria you
set
and where the joined fields at each join have the same value in both
tables.
When you added another table, you imposed another constraint where the
joined field in both the original query and in the new table has to have
the
same value. The amount of records changed because there are less records
in
the new table that match records in the original query where the joined
field have the same value.

Here's a simple example:
QryCustomer is based on TblCustomer with CustomerID as the PK and where
CustomerTypeID = 1 is residential and CustomerTypeID = 2 is commercial.
For
QryCustomer, CustomerTypeID criteria is set as 2 so QryCustomer returns
only
commercial customers. QryCustomer does not return all the records in
TblCustomer. Now say you have TblSale that looks like:
TblSale
SaleID
CustomerID
etc
and you join TblSale to TblCustomer in QryCustomer on CustomerID. Adding
TblSale will constrain QryCustomer to only return commercial customers
whom
you have made a sale to.

Steve



"cjgav" wrote in message
...
Hi
I have a problem in access 2002. When I add a table to a query it
changes
the amount records displayed without me adding any fields or changing
criteria.
Can anyone explain why?

Regards







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.