Difficult query

Difficult query

am 21.04.2008 13:00:56 von themastertaylor

My system tracks orders placed, each record can have up to 6 items on
order, I've set up the following query to locate the price for an
item. basically once we've been invoiced for the goods i go back
through and enter the exact pricing. as such i have a checkbox to
indicate if the prices are corrent etc. the query is as follows

SELECT Orders.supplierid, Orders.[Order Number], Orders.Item,
Orders.Rate, Orders.Item2, Orders.Rate2, Orders.Item3, Orders.Rate3,
Orders.Item4, Orders.Rate4, Orders.Item5, Orders.Rate5, Orders.Item6,
Orders.Rate6, Orders.[Invoice and order checked?], Orders.[Ref Number]
FROM Orders
WHERE (((Orders.Item)=Forms!Orders!Item) And ((Orders.[Invoice and
order checked?])=True)) Or (((Orders.Item2)=Forms!Orders!Item) And
((Orders.[Invoice and order checked?])=True)) Or
(((Orders.Item3)=Forms!Orders!Item) And ((Orders.[Invoice and order
checked?])=True)) Or (((Orders.Item4)=Forms!Orders!Item) And ((Orders.
[Invoice and order checked?])=True)) Or (((Orders.Item5)=Forms!Orders!
Item) And ((Orders.[Invoice and order checked?])=True)) Or
(((Orders.Item6)=Forms!Orders!Item) And ((Orders.[Invoice and order
checked?])=True));

This returns the prices for a product for various sites and suppliers,
where the invoice has been checked. i then have a second query to
filter the results to specific sites and suppliers. the issue i've
noticed is that this only matches up prices for the item in the item
field as opposed to item1, item2 etc.

It looks to me that i need to repeat the WHERE section for the other 5
item fields as another Or satement. however as some orders will only
have one item on them i think the results will bring up a huge number
of blank fields - i.e. if the order i'm searching for prices on has
say 20 bags of cement in item1, the current query will find the price
from another record. however if i have the cement and then in item2 i
have one pack of bricks, the query's not searching for the price on
this. however it would search for a price in another record where the
matching price is in item2. in my mind the above method would work
but would also find prices for a blank field i.e. an order where there
are less than 6 items.

this obviously means a lot of irrelevant data and a massive query
which may take a while to load. is there a simpler way?

Re: Difficult query

am 21.04.2008 14:57:00 von none

wrote in message
news:3397c327-6deb-4af2-a5a7-5e36d21aa436@d45g2000hsc.google groups.com...
> My system tracks orders placed, each record can have up to 6 items on
> order, I've set up the following query to locate the price for an
> item. basically once we've been invoiced for the goods i go back
> through and enter the exact pricing. as such i have a checkbox to
> indicate if the prices are corrent etc. the query is as follows
>
> SELECT Orders.supplierid, Orders.[Order Number], Orders.Item,
> Orders.Rate, Orders.Item2, Orders.Rate2, Orders.Item3, Orders.Rate3,
> Orders.Item4, Orders.Rate4, Orders.Item5, Orders.Rate5, Orders.Item6,
> Orders.Rate6, Orders.[Invoice and order checked?], Orders.[Ref Number]
> FROM Orders
> WHERE (((Orders.Item)=Forms!Orders!Item) And ((Orders.[Invoice and
> order checked?])=True)) Or (((Orders.Item2)=Forms!Orders!Item) And
> ((Orders.[Invoice and order checked?])=True)) Or
> (((Orders.Item3)=Forms!Orders!Item) And ((Orders.[Invoice and order
> checked?])=True)) Or (((Orders.Item4)=Forms!Orders!Item) And ((Orders.
> [Invoice and order checked?])=True)) Or (((Orders.Item5)=Forms!Orders!
> Item) And ((Orders.[Invoice and order checked?])=True)) Or
> (((Orders.Item6)=Forms!Orders!Item) And ((Orders.[Invoice and order
> checked?])=True));
>
> This returns the prices for a product for various sites and suppliers,
> where the invoice has been checked. i then have a second query to
> filter the results to specific sites and suppliers. the issue i've
> noticed is that this only matches up prices for the item in the item
> field as opposed to item1, item2 etc.
>
> It looks to me that i need to repeat the WHERE section for the other 5
> item fields as another Or satement. however as some orders will only
> have one item on them i think the results will bring up a huge number
> of blank fields - i.e. if the order i'm searching for prices on has
> say 20 bags of cement in item1, the current query will find the price
> from another record. however if i have the cement and then in item2 i
> have one pack of bricks, the query's not searching for the price on
> this. however it would search for a price in another record where the
> matching price is in item2. in my mind the above method would work
> but would also find prices for a blank field i.e. an order where there
> are less than 6 items.
>
> this obviously means a lot of irrelevant data and a massive query
> which may take a while to load. is there a simpler way?

A simpler way would be to put your order lines into a 2nd table, 1 record
per part.
[Order Number], [Item], [Rate]....

Your query will become simple and you gain the ability to order more then 6
parts.

Re: Difficult query

am 22.04.2008 07:36:15 von themastertaylor

>
> A simpler way would be to put your order lines into a 2nd table, 1 record
> per part.
> [Order Number], [Item], [Rate]....
>
> Your query will become simple and you gain the ability to order more then 6
> parts.- Hide quoted text -
>
> - Show quoted text -

I think i may understand - are you talking in the sense where i'd have
the two tables an input form then a sub form for the actual ordering
process. sorry to be sketchy but its a live system so i can't really
play around till the weekend when i can back it up and not need to use
it for a couple of days

Re: Difficult query

am 22.04.2008 14:09:56 von none

wrote in message
news:132b5d3e-31ba-4bd0-852d-bd683bcc4b89@m36g2000hse.google groups.com...
>
> >
> > A simpler way would be to put your order lines into a 2nd table, 1
record
> > per part.
> > [Order Number], [Item], [Rate]....
> >
> > Your query will become simple and you gain the ability to order more
then 6
> > parts.- Hide quoted text -
> >
> > - Show quoted text -
>
> I think i may understand - are you talking in the sense where i'd have
> the two tables an input form then a sub form for the actual ordering
> process. sorry to be sketchy but its a live system so i can't really
> play around till the weekend when i can back it up and not need to use
> it for a couple of days

You will have 2 tables, the 1st will contain the [Order number] and any
other common information about the order, Ship to, Bill to, order date ect.
the second will contain the order lines. Your main form will be used to
create the order number and fill-in the order information. The sub form will
be used to enter the Items. Take a look at the Order form in the NorthWind
sample database that comes with Access.