if you used multiple tables then you would avoid zeros altogether:
table1
{
SL#
Date
Client ID
}
PRIMARY KEY = SL#
FOREIGN KEY = ClientID (references table3)
table2
{
SL#
ItemID
NumberOfItemPurchased
PricePerItem
}
PRIMARY KEY = (SL#,ItemID)
FOREIGN KEY = SL# (references table1)
table3
{
ClientID
ClientName
ClientAddress
ClientPhone
ClientContact
}
PRIMARY KEY = (ClientID)
-----------------------------------------------
The relationship between table1 and table2 is one-to-many, so there
will be one record in table1 for each order, and one or more records
in table2 to represent each individual item that was included in order x.
hope this helps,
Owen
--- In [email protected], se e <[EMAIL PROTECTED]> wrote:
> Hi Owen Thanks for the reply
>
> Isn't possible to extract the info with current setup, moreover i've
tried to count the records that are having value more than zero, I've
used the following, but it always gives the same result,
>
> on a calculated field in the report i've given as :
=Count([Textboxname]>0)
>
> owen_group_profile <[EMAIL PROTECTED]> wrote:
> Hi,
>
> it's probably possible to extract the information you need, but I
> think perhaps it'd be easier in the long run if you used two related
> tables instead.
>
> One to record the order number and date of the order etc, and a second
> table to record the details of the items which were ordered (prices of
> the items can even go on a third table if they sell for the same price
> each time?)
>
> Given your current design, it's going to go all pear shaped if someone
> orders 5 items in one go.
>
> Owen
>
>
>
>
> --- In [email protected], se e <[EMAIL PROTECTED]> wrote:
> > Hi
> > This is my second requirement, I am looking for the
following
> >
> > My Table name is: Orders
> >
> > Fields
> >
> > SL # (Auto number)
> >
> > Date (Date/Time)
> >
> > Client Name (Text)
> >
> > Item-1-Value(numbers)
> >
> > Item-2-Value(Numbers)
> >
> > Item-3-Value(numbers)
> >
> > Item-4-Value(Numbers)
> >
> > I need a report to show the number of orders received in a given
> date range in the following category
> >
> > Category for each item. These categories are fixed
> >
> > <=500,"USD 0 To 500"
> >
> > <=1000,"USD 501 To 1,000"
> >
> > <=2000,"USD 1,001 To 2,000"
> >
> > <=4000,"USD 2,001 To 4,000"
> >
> > <=10000,"USD 4,001 To 10,000"
> >
> > >=10001,"USD Over 10,001"
> >
> > I've used a query to show this and I have used the following
expression
> >
> > Category: IIf([Item-1-Value]<=500,"USD 0 To
> 500",IIf([Item-1-Value]<=1000,"USD 501 To
> 1,000",IIf([Item-1-Value]<=2000,"USD 1,001 To
> 2,000",IIf([Item-1-Value]<=4000,"USD 2,001 To
> 4,000",IIf([Item-1-Value]<=10000,"USD 4,001 To
> 10,000",IIf([Item-1-Value]>=10001,"USD Over 10,001"))))))
> >
> >
> >
> > Another problem in my query there zero values also available,
> because my data is as follows
> >
> > SL# Item-1 Item-2 Item-3
> >
> > 1 0 10 0
> >
> > 2 15 0 0
> >
> > 3 0 0 20
> >
> >
> >
> > I need the number of orders > zero for all the items, I tried to
> give a criteria in query grid, but it returned onl the records that
> are matching each other. How can we do this report, please help
> >
> >
> >
> > ---------------------------------
> > Discover Yahoo!
> > Get on-the-go sports scores, stock quotes, news & more. Check it out!
> >
> > [Non-text portions of this message have been removed]
>
>
>
>
> ---------------------------------
> YAHOO! GROUPS LINKS
>
>
> Visit your group "AccessVBACentral" on the web.
>
> To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service.
>
>
> ---------------------------------
>
>
>
>
> ---------------------------------
> Yahoo! Sports
> Rekindle the Rivalries. Sign up for Fantasy Football
>
> [Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessVBACentral/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
