INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help with a query

Need help with a query

(OP)
I have a multi line item order that I need to read into a form with a query where
a couple of line items fields need to be contained on one line for each quantity ordered.

Quantities ordered will vary from sales order to sales order and it is assumed that each
order will contain a line item for the same software for each line item of computer ordered.

I have the following tables:


PC_tbl contains a computer part number, a computer description, and a computer Type:

PartNO . PartDesc . PartType
10001 . Computer IBM . Desktop
10002 . Computer IBM . Laptop
20001 . Computer Dell . Desktop
20002 . Computer Dell . Laptop
30001 . Computer HP . Desktop
30002 . Computer HP . Laptop

Soft_tbl which contains a part number and a description

PartNO . PartDesc
10222 . Bogus Software
10333 . MS Office 2012
10444 . Corel Draw
10555 . Adobe Illustrator


I pull the sales order from an ERP system into a table named
Order_tbl which depending on the order, might resemble something like this:

LineNO . OrderNO . PartNO . PartDesc . Qty
1 . 100100 . 10001 . Computer IBM 2
2 . 100100 . 20002 . Computer Dell 1
3 . 100100 . 10222 . Bogus Software 3

and on a continuous form, I wish to display the following:

Order# . Item . System . Computer . Type . Software
100100 . 1 . Computer IBM . Desktop . Bogus Software
100100 . 2 . Computer IBM . Desktop . Bogus Software
100100 . 3 . Computer Dell . Laptop . Bogus Software

I am having a hard time figuring out the query and so far came up
with the following:

SELECT Order_tbl.OrderNO, Null AS Item, PC_tbl.PartDesc AS System, PC_tbl.PartType AS ComputerType, Soft_tbl.PartDesc AS Software
FROM (Order_tbl LEFT JOIN Soft_tbl ON Order_tbl.PartNo = Soft_tbl.PartNo) LEFT JOIN PC_tbl ON Order_tbl.PartNo = PC_tbl.PartNo;

Also, I wasn't sure how to set the Item (set to Null) in the query.

D0 I need to do this with more than one Query in order to set up each line in a table with one query and then create a query to
use that table's data to populate the form?

What am I missing?


Thanks

RE: Need help with a query

(OP)

Any ideas out there on how to do this with a query or do I need multiple queries?

Thanks

RE: Need help with a query

(OP)
No replies yet, so maybe I need to ask this in a simpler way...

Is there a way to make a query that will take fields of each row of
one table and display them in one row of a query?

thanks

RE: Need help with a query

Part of the problem here (and may be the reason nobody replied) is that it is hard to read your post, i.e. decipher the columns of data in your tables. Below is just my guess of how your data looks like, but that’s just a guess...

PC_tbl 
PartNO   PartDesc        PartType
 10001   Computer IBM    Desktop
 10002   Computer IBM    Laptop 
 20001   Computer Dell   Desktop
 20002   Computer Dell   Laptop
 30001   Computer HP     Desktop
 30002   Computer HP     Laptop 

Soft_tbl 
PartNO   PartDesc
 10222   Bogus Software
 10333   MS Office 2012
 10444   Corel Draw
 10555   Adobe Illustrator

Order_tbl 
LineNO  OrderNO  PartNO  PartDesc       Qty
 1      100100    10001  Computer IBM    2
 2      100100    20002  Computer Dell   1
 3      100100    10222  Bogus Software  3
 
“I wish to display the following:”

Order#  Item System   Computer   Type     Software	
100100   1   Computer    IBM    Desktop   Bogus Software 
100100   2   Computer    IBM    Desktop   Bogus Software 
100100   3   Computer   Dell     Laptop   Bogus Software 
 

Use [PRE] tag to align your data

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Need help with a query

(OP)
Thanks for the tip Andy.

I will try it again...

I have a multi line item order that I need to read into a form with a query where
a couple of line items fields need to be contained on one line for each quantity ordered.

Quantities ordered will vary from sales order to sales order and it is assumed that each
order will contain a line item for the same software for each line item of computer ordered.

I have the following tables:

PC_tbl contains a computer part number, a computer description, and a computer Type:

PartNO	PartDesc	PartType
------	------------	--------
 10001	Computer IBM	Desktop
 10002	Computer IBM	Laptop 
 20001	Computer Dell	Desktop
 20002	Computer Dell	Laptop
 30001	Computer HP	Desktop
 30002	Computer HP	Laptop 
 
 
Soft_tbl which contains a part number and a description

PartNO	PartDesc
------	--------
 10222	Bogus Software
 10333	MS Office 2012
 10444	Corel Draw
 10555	Adobe Illustrator

 


I pull the sales order from an ERP system into a table named
Order_tbl which depending on the order, might resemble something like this:

LineNO	OrderNO	PartNO	PartDesc	Qty
------	-------	------	-------------	---
 1	100100	10001	Computer IBM 	2
 2	100100	20002	Computer Dell 	1
 3	100100	10222	Bogus Software 	3
 


and on a continuous form, I wish to display the following:

Order#	Item	System		Computer	Type		Software
======	====	======		========	=====		=========	
 100100	  1	Computer 	IBM		Desktop		Bogus Software 
 100100	  2	Computer 	IBM		Desktop		Bogus Software 
 100100	  3	Computer 	Dell		Laptop		Bogus Software 



I am having a hard time figuring out the query and so far came up
with the following:

SELECT Order_tbl.OrderNO, Null AS Item, PC_tbl.PartDesc AS System, PC_tbl.PartType AS ComputerType, Soft_tbl.PartDesc AS Software
FROM (Order_tbl LEFT JOIN Soft_tbl ON Order_tbl.PartNo = Soft_tbl.PartNo) LEFT JOIN PC_tbl ON Order_tbl.PartNo = PC_tbl.PartNo;

Also, I wasn't sure how to set the Item (set to Null) in the query.

D0 I need to do this with more than one Query in order to set up each line in a table with one query and then create a query to
use that table's data to populate the form?

What am I missing?

Thanks

RE: Need help with a query

"I wish to display the following:"
Order#    comes from Order_tbl.OrderNO
Item      comes from Order_tbl.LineNO
System    comes from ??? (first  part of PC_tbl.PartDesc?)
Computer  comes from ??? (second part of PC_tbl.PartDesc?)
Type      comes from PC_tbl.PartType
Software  comes from Soft_tbl.PartDesc or Order_tbl.PartDesc 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Need help with a query

(OP)
LineNO	OrderNO	PartNO	PartDesc	Qty
------	-------	------	-------------	---
 1	100100	10001	Computer IBM 	2
 2	100100	20002	Computer Dell	1
 3	100100	10222	Bogus Software	3 


Order# comes from Order_tbl.OrderNO
Item comes from Order_tbl.LineNO

One item per line per Qty ordered
Item 1 = 1 of 2 Computer IBM,
Item 2 = 2 of 2 Computer IBM,

Item 3 = 1 of 1 Computer Dell


System comes from ??? (first part of PC_tbl.PartDesc?)
Computer comes from ??? (second part of PC_tbl.PartDesc?)
Type comes from PC_tbl.PartType
Software comes from Soft_tbl.PartDesc or Order_tbl.PartDesc

RE: Need help with a query

So, what you want is 3 lines because you sold 3 computers and 3 software systems?

How would you want it displayed if this was your Order_tbl?

LineNO     OrderNO     PartNO     PartDesc       Qty
1          100100      10001      Computer IBM   2
2          100100      20002      Computer Dell  2
3          100100      10222      Bogus Software 1
4          100100      10444      Corel Draw     3 


How do you know what software goes with what hardware?

Randy

RE: Need help with a query

(OP)
Thanks Randy-

99.999% of the time there is only one computer type per order and the software ordered quantity
equals the computer ordered quantity, so I am fine with making this assumption...

RE: Need help with a query

(OP)
OK, I figured out a work around for this by using several queries, one at a time..

Thanks everyone for your help and feedback

RE: Need help with a query

Care to share the "work around" solution?

Randy

RE: Need help with a query

(OP)
Not much to share really.

What I posted was a smaller sample of the database that I am working on as I was mostly wondering about the concept
of how to get the data into a format that I could use...

I realized that I was attempting to do too much at one time with a query that had several joins to other tables and was
limiting my data results so I pared this down by making several queries that create tables and in the subsequent queries
use the table created from the previous query.

This allowed me to get the data into the format that I wanted.

Thanks for sharing your ideas.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close