Need help with a query

[robojeff]

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

 

[robojeff]

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

Thanks

 

[robojeff]

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

 

[Andrzejek]

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...

[pre]
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
[/pre]
“I wish to display the following:”

[pre]
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
[/pre]

Use [PRE] tag to align your data

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[robojeff]

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:

[pre]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

[/pre]
Soft_tbl which contains a part number and a description

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

[/pre]

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:

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

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

[pre]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[/pre]


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

 

[Andrzejek]

I wish to display the following:"[pre]
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[/pre]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[robojeff]

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


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

 

[randy700]

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?

[pre]
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[/pre]


How do you know what software goes with what hardware?

Randy

 

[robojeff]

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...

 

[robojeff]

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

Thanks everyone for your help and feedback

 

[randy700]

Care to share the "work around" solution?


Randy

 

[robojeff]

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.