Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-11-11
5
Medium Priority
?
189 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:su2117
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 12557652
what is the field that is common to both tables?
0
 

Author Comment

by:su2117
ID: 12557929
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
 
LVL 39

Accepted Solution

by:
stevbe earned 1500 total points
ID: 12558159
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
 

Author Comment

by:su2117
ID: 12562821
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12566175
"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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question