need to select data from the orders table based on order number in a query of overdue orders

Hi
I have 2 tables
table 1 Orders with all order  
              fields are
                  OrderNumber ordertype orderline OrderDate item Qtyordred unitcost DateRequested Datepicked date Shipped
                       Table has 130,000 records


table 2 is the Customerorder table  
     fields there
                 customerNumber customer ordernumber customertype
     this has 16000 records so I wrote a query (select) to pick only the IT customers which has 3000 records


Here is what i need  
          Based on a customertype of IT in the customerOrder table I have to find the order details in the Order table that match
        orders.ordernumber=customerOrder.ordernumber where the customerorder.ordertype is "IT"

I tried all the option in the   design view the computer locks up for hours with no result


Please Help - I am fairly new to Access

Regards
Subi
su2117Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
what is the field that is common to both tables?
0
su2117Author Commented:
Here is the sample of the data

OrderNumber ordertype orderline OrderDate item       Qtyordred unitcost DateRequested Datepicked date Shipped
C1234            CI           1            10/12/04    K34        10            12.36   11/04/2004      NULL                 NuLL
C1234            CI          2            10/12/04    K34         10            12.36   12/04/2004      NULL                 NuLL
C1234            CI          3            10/12/04    K35         10            12.36   11/04/2004      NULL                 NuLL
C1234            CI           4            10/12/04    K36        10            12.36   11/04/2004      NULL                 NuLL
C1234            CI          5            10/12/04    K47         10            12.36   12/04/2004      NULL                 NuLL
C1234            CI          6            10/12/04    K53         10            12.36   11/04/2004      NULL                 NuLL
C1235           CI           1            10/12/04    K34        10            12.36   11/04/2004      NULL                 NuLL
C1235            CI          2            10/12/04    K34         10            12.36   12/04/2004      NULL                 NuLL
C1235            CI          3            10/12/04    K35         10            12.36   11/04/2004      NULL                 NuLL
C1235            CI           4            10/12/04    K36        10            12.36   11/04/2004      NULL                 NuLL
C1235            CI          5            10/12/04    K47         10            12.36   12/04/2004      NULL                 NuLL
C1235            CI          6            10/12/04    K53         10            12.36   11/04/2004      NULL                 NuLL
C1237           CI           1            10/12/04    K34        10            12.36   11/04/2004      NULL                 NuLL
C1237            CI          2            10/12/04    K34         10            12.36   12/04/2004      NULL                 NuLL
C1237            CI          3            10/12/04    K35         10            12.36   11/04/2004      NULL                 NuLL




CustomerNumber           Order number               type
AB123                              C1222                 CN
AB124                        C1234                  IT
JK223                         C1444                  MM
SR211                        CI1235                 XX
AB124                        C1237                  IT





DATA OUTPUT
Customer   customer   Order         order   Date        Item      qty   Unit       Value                requested
number        Type        Number     Line    Ordered                         Price     (qty*unitPrice) date

AB124           IT          C1234        1            10/12/04    K34   10   12.36    120.36                    11/04/2004      
AB124           IT          C1234        2            10/12/04    K34         10            12.36   12/04/
AB124           IT          C1234        3            10/12/04    K35         10            12.36   11/04/2004  
AB124           IT          C1234        4            10/12/04    K36        10            12.36   11/04/2004      
AB124           IT          C1234        5            10/12/04    K47         10            12.36   12/04/2004    
AB124           IT          C1234        6            10/12/04    K53         10            12.36   11/04/2004      


So on …
AB124           IT          C1237       1            10/12/04    K34        10            12.36   11/04/2004        
AB124           IT         C1237        2            10/12/04    K34         10            12.36   12/04/2004      
AB124           IT         C1237        3            10/12/04    K35         10            12.36   11/04/2004

though an example would help - so I did this
Regards
Subi
0
stevbeCommented:
ok ... so OrderNumber is the comon field ...

Add both tables to your query and left mouse down on the OrderNumber field from the Orders table to OrderNumber in the Customerorder table. Now drag the field from each table onto the design grid to select the fields to display. Make sure that you include the customer type field and add "IT" to the criteria row. If this is what you aleready have and it is still very slow then make sure that the OrderNumber field in both tables is indexed and that the customer type field is indexed also.

Steve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
su2117Author Commented:
Hi Steve
I was hoping for a way to optimize...
However could you give me some ways to solve the following

Kit              Kit               Item1   Item1    Item2    Item2    item3   item3    item4   item4   item5   item5
Number      Description   Code    Qty         Code    Qty       Code    Qty       Code    Qty      Code    Qty        
1234       raadfadf           1122   1           1332     2          1555     5           1111    1        1236     12


and so on  lot of rows and columns
is there an easy way to
makeit
Kit number kit description item     qty
1234         raadfdf            1122     1
1234         raadfdf            1332     2
1234         raadfdf            1555     5
1234         raadfdf            1111     1
1234         raadfdf            1236    12

thanks
Subi
By the way I was trying to run initial of two queries I later wrote  an append query to write the data to a table then added index on the the fields needed and ran it with a third querry it worked a little faster.
0
stevbeCommented:
"Kit              Kit               Item1   Item1    Item2    Item2    item3   item3    item4   item4   item5   item5
Number      Description   Code    Qty         Code    Qty       Code    Qty       Code    Qty      Code    Qty        
1234       raadfadf           1122   1           1332     2          1555     5           1111    1        1236     12"

this looks like a crosstab query ... change it back to a select query.

These look like entirely different tables and fields, is this a new question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.