Advanced Query question

G

Guest

I need to know if there is a way to do this in a query and if someone could
supply the SQL for it. I have a table with 2 fields, OrderNumber and
ItemNumber. There is a years worth of data. The order number will be unique
but the items will appear across many different orders. What I want to be
able to do is determine if ITEM1 sold on X amount of orders, these other
items sold with it Z percentage of the time. So mabye the query determines
the item that sells the most and the other items appear on the same order X
percent of the time. Then the next line of data is the 2nd fastest selling
item appeared on x amount of orders and the following items sold with it x
percent of the time and so on. I want to avoid having to do this one item at
a time by entering each one becuas there is way too much data.

Can this be done with 1 query, or multiple query's? Could it be done with
code?

Thanks,
Sean
 
G

Gary Walter

Sean said:
I need to know if there is a way to do this in a query and if someone could
supply the SQL for it. I have a table with 2 fields, OrderNumber and
ItemNumber. There is a years worth of data. The order number will be
unique
but the items will appear across many different orders. What I want to be
able to do is determine if ITEM1 sold on X amount of orders, these other
items sold with it Z percentage of the time. So mabye the query
determines
the item that sells the most and the other items appear on the same order
X
percent of the time. Then the next line of data is the 2nd fastest
selling
item appeared on x amount of orders and the following items sold with it x
percent of the time and so on. I want to avoid having to do this one item
at
a time by entering each one becuas there is way too much data.

Can this be done with 1 query, or multiple query's? Could it be done with
code?
Click to expand...
I could be wrong but I imagine Access would not
support the amount of subquery nesting you would
need.

I imagine setting up a table w/ 4 fields

tblCommonItems

ItemNumber Text
OrderCnt Long
CommonItem Text
CommonCnt Long

then following code for a command button:

'**** start code *****
Private Sub cmdGetCommonItems_Click()
On Error GoTo Err_cmdGetCommonItems_Click

Dim strTblName As String
Dim strItemNumber As String 'maybe long?
Dim db As DAO.Database
Dim rs As DAO.Recordset

strTblName = "qryOrderItem"

DoCmd.Hourglass True

Set db = CurrentDb

'clear table
db.Execute "DELETE * FROM tblCommonItems", dbFailOnError

'open a recordset to distinct items.

strSQL = "SELECT DISTINCT ItemNumber FROM " & strTblName _
& " ORDER BY ItemNumber;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF = True
strItemNumber = rs!ItemNumber

strSQL = "INSERT INTO tblCommonItems " _
& "(ItemNumber, CommonItem, CommonCnt) " _
& "SELECT '" & strItemNumber & "', " _
& "M.ItemNumber, COUNT(*) FROM " _
& strTblName & " As M WHERE " _
& "M.ItemNumber <> '" & strItemNumber & "' AND " _
& "M.OrderNumber IN (SELECT q.OrderNumber " _
& "FROM " & strTblName & " As q WHERE " _
& "q.ItemNumber = '" & strItemNumber & "') " _
& "GROUP BY M.ItemNumber;"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE tblCommonItems " _
& "SET OrderCnt = DCount('ItemNumber'," _
& "'" & strTblName & "','[ItemNumber]=''" _
& strItemNumber & "''') WHERE ItemNumber ='" _
& strItemNumber & "';"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

rs.MoveNext
Loop

rs.Close
db.Close

MsgBox "Successfully filled tblCommonItems."

Exit_cmdGetCommonItems_Click:
If Not rs Is Nothing Then Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing
DoCmd.Hourglass False
Exit Sub

Err_cmdGetCommonItems_Click:
MsgBox Err.Description
Resume Exit_cmdGetCommonItems_Click

End Sub
'**** end code ****

I made a simple table

tblSean

OrderNumber ItemNumber
1 Itm1
1 Itm2
1 Itm4
2 Itm1
2 Itm3
3 Itm2
3 Itm3
3 Itm4
4 Itm2
4 Itm3
4 Itm4
5 Itm1
5 Itm2
6 Itm3
6 Itm4

then defined a query

qryOrderItem

SELECT tblSean.OrderNumber, tblSean.ItemNumber
FROM tblSean;

make above query using your table
(maybe even filtering for order dates...)

I then ran the sub above and got:

tblCommonItems

ItemNumber OrderCnt CommonItem CommonCnt
Itm1 3 Itm2 2
Itm1 3 Itm3 1
Itm1 3 Itm4 1
Itm2 4 Itm1 2
Itm2 4 Itm3 2
Itm2 4 Itm4 3
Itm3 4 Itm1 1
Itm3 4 Itm2 2
Itm3 4 Itm4 3
Itm4 4 Itm1 1
Itm4 4 Itm2 3
Itm4 4 Itm3 3


Let's see if thinking right....
OrderCnt
CommonCnt
Itm1 was in "orders" 1 2 5 3
Itm2 in same? x x 2
Itm3 in same? x 1
Itm4 in same? x 1

Itm2 was in "orders" 1 3 4 5 4
Itm1 in same? x x 2
Itm3 in same? x x 2
Itm4 in same? x x x 3

It *looks* like it was working right....
 
G

Gary Walter

It occurs to me that you should add
a primary key, autonumber field
(say "CIID") to tblCommonItems.

then, for a report that gives you
top 5 sales items and their correponding
top 5 items that sold in same orders
(like Amazon shows you "x % of people
who bought this also bought..")


qryTop5ItemSales

SELECT DISTINCT TOP 5
t.ItemNumber,
t.OrderCnt
FROM
tblCommonItems As t
ORDER BY
t.OrderCnt DESC ,
t.ItemNumber DESC;

qryrptTop5ItemSalesTop5Common:

SELECT
q.ItemNumber,
q.OrderCnt,
t.CommonItem,
t.CommonCnt,
t.CommonCnt/q.OrderCnt As PerCentOfOrders
FROM
qryTop5ItemSales AS q
INNER JOIN
tblCommonItems AS t
ON
q.ItemNumber = t.ItemNumber
WHERE
t.CIID IN
(SELECT TOP 5 a.CIID
FROM tblCommonItems As a
WHERE
a.ItemNumber = q.ItemNumber
ORDER BY
a.CommonCnt,
a.CommonItem);

I may have made it more complicated
than it needed to be....this is just a method
that came to me first, and I think it will work...

Gary Walter said:
Sean said:
I need to know if there is a way to do this in a query and if someone
could
supply the SQL for it. I have a table with 2 fields, OrderNumber and
ItemNumber. There is a years worth of data. The order number will be
unique
but the items will appear across many different orders. What I want to
be
able to do is determine if ITEM1 sold on X amount of orders, these other
items sold with it Z percentage of the time. So mabye the query
determines
the item that sells the most and the other items appear on the same order
X
percent of the time. Then the next line of data is the 2nd fastest
selling
item appeared on x amount of orders and the following items sold with it
x
percent of the time and so on. I want to avoid having to do this one
item
at
a time by entering each one becuas there is way too much data.

Can this be done with 1 query, or multiple query's? Could it be done
with
code?
Click to expand...
I could be wrong but I imagine Access would not
support the amount of subquery nesting you would
need.

I imagine setting up a table w/ 4 fields

tblCommonItems

ItemNumber Text
OrderCnt Long
CommonItem Text
CommonCnt Long

then following code for a command button:

'**** start code *****
Private Sub cmdGetCommonItems_Click()
On Error GoTo Err_cmdGetCommonItems_Click

Dim strTblName As String
Dim strItemNumber As String 'maybe long?
Dim db As DAO.Database
Dim rs As DAO.Recordset

strTblName = "qryOrderItem"

DoCmd.Hourglass True

Set db = CurrentDb

'clear table
db.Execute "DELETE * FROM tblCommonItems", dbFailOnError

'open a recordset to distinct items.

strSQL = "SELECT DISTINCT ItemNumber FROM " & strTblName _
& " ORDER BY ItemNumber;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF = True
strItemNumber = rs!ItemNumber

strSQL = "INSERT INTO tblCommonItems " _
& "(ItemNumber, CommonItem, CommonCnt) " _
& "SELECT '" & strItemNumber & "', " _
& "M.ItemNumber, COUNT(*) FROM " _
& strTblName & " As M WHERE " _
& "M.ItemNumber <> '" & strItemNumber & "' AND " _
& "M.OrderNumber IN (SELECT q.OrderNumber " _
& "FROM " & strTblName & " As q WHERE " _
& "q.ItemNumber = '" & strItemNumber & "') " _
& "GROUP BY M.ItemNumber;"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE tblCommonItems " _
& "SET OrderCnt = DCount('ItemNumber'," _
& "'" & strTblName & "','[ItemNumber]=''" _
& strItemNumber & "''') WHERE ItemNumber ='" _
& strItemNumber & "';"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

rs.MoveNext
Loop

rs.Close
db.Close

MsgBox "Successfully filled tblCommonItems."

Exit_cmdGetCommonItems_Click:
If Not rs Is Nothing Then Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing
DoCmd.Hourglass False
Exit Sub

Err_cmdGetCommonItems_Click:
MsgBox Err.Description
Resume Exit_cmdGetCommonItems_Click

End Sub
'**** end code ****

I made a simple table

tblSean

OrderNumber ItemNumber
1 Itm1
1 Itm2
1 Itm4
2 Itm1
2 Itm3
3 Itm2
3 Itm3
3 Itm4
4 Itm2
4 Itm3
4 Itm4
5 Itm1
5 Itm2
6 Itm3
6 Itm4

then defined a query

qryOrderItem

SELECT tblSean.OrderNumber, tblSean.ItemNumber
FROM tblSean;

make above query using your table
(maybe even filtering for order dates...)

I then ran the sub above and got:

tblCommonItems

ItemNumber OrderCnt CommonItem CommonCnt
Itm1 3 Itm2 2
Itm1 3 Itm3 1
Itm1 3 Itm4 1
Itm2 4 Itm1 2
Itm2 4 Itm3 2
Itm2 4 Itm4 3
Itm3 4 Itm1 1
Itm3 4 Itm2 2
Itm3 4 Itm4 3
Itm4 4 Itm1 1
Itm4 4 Itm2 3
Itm4 4 Itm3 3


Let's see if thinking right....
OrderCnt
CommonCnt
Itm1 was in "orders" 1 2 5 3
Itm2 in same? x x 2
Itm3 in same? x 1
Itm4 in same? x 1

Itm2 was in "orders" 1 3 4 5 4
Itm1 in same? x x 2
Itm3 in same? x x 2
Itm4 in same? x x x 3

It *looks* like it was working right....
Click to expand...
 
G

Guest

Can you offer some further assistance? I am new with query's of this nature
and I got it to work but it is not comparing the data, it's pulling the same.
Here is some results and the code. Any ideas/

q.material q.delivery t.material t.delivery PerCentOfOrders
Y37056 86385134 Y37056 86385134 1
Y37055 86385134 Y37055 86385134 1
Y37054 86385134 Y37054 86385134 1
Y37053 86385134 Y37053 86385134 1
Y37052 86385134 Y37052 86385134 1

SELECT
q.material,
q.delivery,
t.material,
t.delivery,
t.delivery/q.delivery As PerCentOfOrders
FROM
top50items AS q
INNER JOIN
CommonItems AS t
ON
q.material = t.material
WHERE
t.CIID IN
(SELECT TOP 50 a.CIID
FROM CommonItems As a
WHERE
a.material = q.material
ORDER BY
a.delivery,
a.material);




Gary Walter said:
It occurs to me that you should add
a primary key, autonumber field
(say "CIID") to tblCommonItems.

then, for a report that gives you
top 5 sales items and their correponding
top 5 items that sold in same orders
(like Amazon shows you "x % of people
who bought this also bought..")


qryTop5ItemSales

SELECT DISTINCT TOP 5
t.ItemNumber,
t.OrderCnt
FROM
tblCommonItems As t
ORDER BY
t.OrderCnt DESC ,
t.ItemNumber DESC;

qryrptTop5ItemSalesTop5Common:

SELECT
q.ItemNumber,
q.OrderCnt,
t.CommonItem,
t.CommonCnt,
t.CommonCnt/q.OrderCnt As PerCentOfOrders
FROM
qryTop5ItemSales AS q
INNER JOIN
tblCommonItems AS t
ON
q.ItemNumber = t.ItemNumber
WHERE
t.CIID IN
(SELECT TOP 5 a.CIID
FROM tblCommonItems As a
WHERE
a.ItemNumber = q.ItemNumber
ORDER BY
a.CommonCnt,
a.CommonItem);

I may have made it more complicated
than it needed to be....this is just a method
that came to me first, and I think it will work...

Gary Walter said:
Sean said:
I need to know if there is a way to do this in a query and if someone
could
supply the SQL for it. I have a table with 2 fields, OrderNumber and
ItemNumber. There is a years worth of data. The order number will be
unique
but the items will appear across many different orders. What I want to
be
able to do is determine if ITEM1 sold on X amount of orders, these other
items sold with it Z percentage of the time. So mabye the query
determines
the item that sells the most and the other items appear on the same order
X
percent of the time. Then the next line of data is the 2nd fastest
selling
item appeared on x amount of orders and the following items sold with it
x
percent of the time and so on. I want to avoid having to do this one
item
at
a time by entering each one becuas there is way too much data.

Can this be done with 1 query, or multiple query's? Could it be done
with
code?
Click to expand...
I could be wrong but I imagine Access would not
support the amount of subquery nesting you would
need.

I imagine setting up a table w/ 4 fields

tblCommonItems

ItemNumber Text
OrderCnt Long
CommonItem Text
CommonCnt Long

then following code for a command button:

'**** start code *****
Private Sub cmdGetCommonItems_Click()
On Error GoTo Err_cmdGetCommonItems_Click

Dim strTblName As String
Dim strItemNumber As String 'maybe long?
Dim db As DAO.Database
Dim rs As DAO.Recordset

strTblName = "qryOrderItem"

DoCmd.Hourglass True

Set db = CurrentDb

'clear table
db.Execute "DELETE * FROM tblCommonItems", dbFailOnError

'open a recordset to distinct items.

strSQL = "SELECT DISTINCT ItemNumber FROM " & strTblName _
& " ORDER BY ItemNumber;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF = True
strItemNumber = rs!ItemNumber

strSQL = "INSERT INTO tblCommonItems " _
& "(ItemNumber, CommonItem, CommonCnt) " _
& "SELECT '" & strItemNumber & "', " _
& "M.ItemNumber, COUNT(*) FROM " _
& strTblName & " As M WHERE " _
& "M.ItemNumber <> '" & strItemNumber & "' AND " _
& "M.OrderNumber IN (SELECT q.OrderNumber " _
& "FROM " & strTblName & " As q WHERE " _
& "q.ItemNumber = '" & strItemNumber & "') " _
& "GROUP BY M.ItemNumber;"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE tblCommonItems " _
& "SET OrderCnt = DCount('ItemNumber'," _
& "'" & strTblName & "','[ItemNumber]=''" _
& strItemNumber & "''') WHERE ItemNumber ='" _
& strItemNumber & "';"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

rs.MoveNext
Loop

rs.Close
db.Close

MsgBox "Successfully filled tblCommonItems."

Exit_cmdGetCommonItems_Click:
If Not rs Is Nothing Then Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing
DoCmd.Hourglass False
Exit Sub

Err_cmdGetCommonItems_Click:
MsgBox Err.Description
Resume Exit_cmdGetCommonItems_Click

End Sub
'**** end code ****

I made a simple table

tblSean

OrderNumber ItemNumber
1 Itm1
1 Itm2
1 Itm4
2 Itm1
2 Itm3
3 Itm2
3 Itm3
3 Itm4
4 Itm2
4 Itm3
4 Itm4
5 Itm1
5 Itm2
6 Itm3
6 Itm4

then defined a query

qryOrderItem

SELECT tblSean.OrderNumber, tblSean.ItemNumber
FROM tblSean;

make above query using your table
(maybe even filtering for order dates...)

I then ran the sub above and got:

tblCommonItems

ItemNumber OrderCnt CommonItem CommonCnt
Itm1 3 Itm2 2
Itm1 3 Itm3 1
Itm1 3 Itm4 1
Itm2 4 Itm1 2
Itm2 4 Itm3 2
Itm2 4 Itm4 3
Itm3 4 Itm1 1
Itm3 4 Itm2 2
Itm3 4 Itm4 3
Itm4 4 Itm1 1
Itm4 4 Itm2 3
Itm4 4 Itm3 3


Let's see if thinking right....
OrderCnt
CommonCnt
Itm1 was in "orders" 1 2 5 3
Itm2 in same? x x 2
Itm3 in same? x 1
Itm4 in same? x 1

Itm2 was in "orders" 1 3 4 5 4
Itm1 in same? x x 2
Itm3 in same? x x 2
Itm4 in same? x x x 3

It *looks* like it was working right....
Click to expand...
Click to expand...
 
G

Gary Walter

How about this first....

create the tblCommonItems
with same field names and type
as in my example.


tblCommonItems
CIID autonumber, primary key
ItemNumber Text
OrderCnt Long
CommonItem Text
CommonCnt Long


create a form with a command button,
name it "cmdGetCommonItems"

put code I wrote for you in its Click Event.

create "qryOrderItem"

SELECT
material As OrderNumber,
delivery As ItemNumber
FROM
yurtable;

change "material," "delivery,"
and "yurtable" to applicable
field names and table name
from your database.

***But leave the aliases***

Now, click on command button
to fill tblCommonItems.

then, try my two queries

qryTop5ItemSales

qryrptTop5ItemSalesTop5Common

to at least report back here if it works....

If it does work, then make your adjustments
based on a fully working model....

If it doesn't work, it worked here with simple
data, so we will have to find out what
is different between my working model
and your non-working model.

does that sound okay?


Sean said:
Can you offer some further assistance? I am new with query's of this
nature
and I got it to work but it is not comparing the data, it's pulling the
same.
Here is some results and the code. Any ideas/

q.material q.delivery t.material t.delivery PerCentOfOrders
Y37056 86385134 Y37056 86385134 1
Y37055 86385134 Y37055 86385134 1
Y37054 86385134 Y37054 86385134 1
Y37053 86385134 Y37053 86385134 1
Y37052 86385134 Y37052 86385134 1

SELECT
q.material,
q.delivery,
t.material,
t.delivery,
t.delivery/q.delivery As PerCentOfOrders
FROM
top50items AS q
INNER JOIN
CommonItems AS t
ON
q.material = t.material
WHERE
t.CIID IN
(SELECT TOP 50 a.CIID
FROM CommonItems As a
WHERE
a.material = q.material
ORDER BY
a.delivery,
a.material);




Gary Walter said:
It occurs to me that you should add
a primary key, autonumber field
(say "CIID") to tblCommonItems.

then, for a report that gives you
top 5 sales items and their correponding
top 5 items that sold in same orders
(like Amazon shows you "x % of people
who bought this also bought..")


qryTop5ItemSales

SELECT DISTINCT TOP 5
t.ItemNumber,
t.OrderCnt
FROM
tblCommonItems As t
ORDER BY
t.OrderCnt DESC ,
t.ItemNumber DESC;

qryrptTop5ItemSalesTop5Common:

SELECT
q.ItemNumber,
q.OrderCnt,
t.CommonItem,
t.CommonCnt,
t.CommonCnt/q.OrderCnt As PerCentOfOrders
FROM
qryTop5ItemSales AS q
INNER JOIN
tblCommonItems AS t
ON
q.ItemNumber = t.ItemNumber
WHERE
t.CIID IN
(SELECT TOP 5 a.CIID
FROM tblCommonItems As a
WHERE
a.ItemNumber = q.ItemNumber
ORDER BY
a.CommonCnt,
a.CommonItem);

I may have made it more complicated
than it needed to be....this is just a method
that came to me first, and I think it will work...

Gary Walter said:
:
I need to know if there is a way to do this in a query and if someone
could
supply the SQL for it. I have a table with 2 fields, OrderNumber and
ItemNumber. There is a years worth of data. The order number will be
unique
but the items will appear across many different orders. What I want
to
be
able to do is determine if ITEM1 sold on X amount of orders, these
other
items sold with it Z percentage of the time. So mabye the query
determines
the item that sells the most and the other items appear on the same
order
X
percent of the time. Then the next line of data is the 2nd fastest
selling
item appeared on x amount of orders and the following items sold with
it
x
percent of the time and so on. I want to avoid having to do this one
item
at
a time by entering each one becuas there is way too much data.

Can this be done with 1 query, or multiple query's? Could it be done
with
code?

I could be wrong but I imagine Access would not
support the amount of subquery nesting you would
need.

I imagine setting up a table w/ 4 fields

tblCommonItems

ItemNumber Text
OrderCnt Long
CommonItem Text
CommonCnt Long

then following code for a command button:

'**** start code *****
Private Sub cmdGetCommonItems_Click()
On Error GoTo Err_cmdGetCommonItems_Click

Dim strTblName As String
Dim strItemNumber As String 'maybe long?
Dim db As DAO.Database
Dim rs As DAO.Recordset

strTblName = "qryOrderItem"

DoCmd.Hourglass True

Set db = CurrentDb

'clear table
db.Execute "DELETE * FROM tblCommonItems", dbFailOnError

'open a recordset to distinct items.

strSQL = "SELECT DISTINCT ItemNumber FROM " & strTblName _
& " ORDER BY ItemNumber;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF = True
strItemNumber = rs!ItemNumber

strSQL = "INSERT INTO tblCommonItems " _
& "(ItemNumber, CommonItem, CommonCnt) " _
& "SELECT '" & strItemNumber & "', " _
& "M.ItemNumber, COUNT(*) FROM " _
& strTblName & " As M WHERE " _
& "M.ItemNumber <> '" & strItemNumber & "' AND " _
& "M.OrderNumber IN (SELECT q.OrderNumber " _
& "FROM " & strTblName & " As q WHERE " _
& "q.ItemNumber = '" & strItemNumber & "') " _
& "GROUP BY M.ItemNumber;"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE tblCommonItems " _
& "SET OrderCnt = DCount('ItemNumber'," _
& "'" & strTblName & "','[ItemNumber]=''" _
& strItemNumber & "''') WHERE ItemNumber ='" _
& strItemNumber & "';"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

rs.MoveNext
Loop

rs.Close
db.Close

MsgBox "Successfully filled tblCommonItems."

Exit_cmdGetCommonItems_Click:
If Not rs Is Nothing Then Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing
DoCmd.Hourglass False
Exit Sub

Err_cmdGetCommonItems_Click:
MsgBox Err.Description
Resume Exit_cmdGetCommonItems_Click

End Sub
'**** end code ****

I made a simple table

tblSean

OrderNumber ItemNumber
1 Itm1
1 Itm2
1 Itm4
2 Itm1
2 Itm3
3 Itm2
3 Itm3
3 Itm4
4 Itm2
4 Itm3
4 Itm4
5 Itm1
5 Itm2
6 Itm3
6 Itm4

then defined a query

qryOrderItem

SELECT tblSean.OrderNumber, tblSean.ItemNumber
FROM tblSean;

make above query using your table
(maybe even filtering for order dates...)

I then ran the sub above and got:

tblCommonItems

ItemNumber OrderCnt CommonItem CommonCnt
Itm1 3 Itm2 2
Itm1 3 Itm3 1
Itm1 3 Itm4 1
Itm2 4 Itm1 2
Itm2 4 Itm3 2
Itm2 4 Itm4 3
Itm3 4 Itm1 1
Itm3 4 Itm2 2
Itm3 4 Itm4 3
Itm4 4 Itm1 1
Itm4 4 Itm2 3
Itm4 4 Itm3 3


Let's see if thinking right....
OrderCnt
CommonCnt
Itm1 was in "orders" 1 2 5 3
Itm2 in same? x x 2
Itm3 in same? x 1
Itm4 in same? x 1

Itm2 was in "orders" 1 3 4 5 4
Itm1 in same? x x 2
Itm3 in same? x x 2
Itm4 in same? x x x 3

It *looks* like it was working right....
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

create "qryOrderItem"

SELECT
material As OrderNumber,
delivery As ItemNumber
FROM
yurtable;

change "material," "delivery,"
and "yurtable" to applicable
field names and table name
from your database.

***But leave the aliases***

of course, if the field names
are already

OrderNumber
ItemNumber

as stated in first post

" I have a table with 2 fields, OrderNumber and
ItemNumber. "

then, you won't need aliases of course,
just change "yurtable" to name of your table
in query "qryOrderItem"

SELECT
OrderNumber,
ItemNumber
FROM
yurtable;

(I don't know your skill level, so
apologies if this was obvious to you)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating a QTY discount 3
Complicated 4 Table Query. 1
Two tables, multiple relationships? 1
Queries hurting my head! 3
Comparing fields from two different tables to give quantity remain 2
Append current object to a new table 1
DROP LETTER 1
Query Question 3

Top