Uncertain of query results - can anyone advise?

[Support]

Hi,
I have 3 tables:
orders 65k records
orderlines 130k records
userswithorders 45k records

I have run a query to find users who have ordered from dept footwear/
menswear and womenswear. Under the footwear dept, there is a subdept called
youthfootwear so I have excluded this by running one query that(briefly):
query dept for having criteria like *wear
query subdept for having criteria not like youth*
I have put a count on the users returned so that only 1 record per user
This returns 29909 records

I have then run another query to get all other orders except for youth
footwear:
query dept for having criteria not like *wear
query subdept for having criteria not like youth*
This returns 45500 records

I then ran a query for just youth footwear and that came back at only 1000
odd records.

Do these results look right?

Also, is it possible to run a NOT query on more than one criteria? EG I
would want to query criteria for NOT having *wear NOR belts?
Many thanks

 

[Jeff Boyce]

We can't see your tables, so we don't know what you are recording in them.
A query, any query, is (generally) based on underlying data.

How are your tables related, what data is stored in them, what is the SQL of
your queries?

 

[Support]

OK, sure - didn't want to put too much on the email ;-) here we go:

tblUsersWithOrders (45k records):
g_user_id, ordergroup_id, order_number, salutation, firstname, lastname,
Gender, u_dateOfBirth, Shipping_FirstName, Shipping_LastName,
Shipping_AddressLine1, Shipping_AddressLine2, Shipping_AddressLine3,
Shipping_Town, Shipping_County, Shipping_Postcode, Shipping_Country,
Billing_FirstName, Billing_LastName, Billing_Address Line1,
Billing_AddressLine2, Billing_AddressLine3, Billing_Town ,Billing_County,
Billing_Postcode, Billing_Country,

tblOrders (65k records)
OrderGroup_id, order_number, order status, Order Date, Catalogue, Payment,
Total Ex Shipping, Total Inc Shipping, Shipping

tblOrderLines (130k records)
Order ID, Catalogue, Product ID, Variant ID, Description, Dept, Sub Dept,
Qty, total per item, cost price

tblUsersWithOrders has a primary key ordergroup_id which is linked to
tblOrders.OrderGroup_id which is in turn linked to tblOrderlines.Order ID

My queries are:
1) to return all records from dept footwear, menswear and womenswear but not
to include subdept youth (we have youth as a subdept and youthfootwear)
SELECT Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], Count(UsersWithOrders.g_user_id) AS
CountOfg_user_id, UsersWithOrders.salutation, UsersWithOrders.firstname,
UsersWithOrders.lastname
FROM UsersWithOrders INNER JOIN (Orders INNER JOIN Orderlines ON
Orders.OrderGroup_id = Orderlines.[Order ID]) ON
UsersWithOrders.ordergroup_id = Orders.OrderGroup_id
GROUP BY Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], UsersWithOrders.salutation,
UsersWithOrders.firstname, UsersWithOrders.lastname
HAVING (((Orderlines.Dept) Like "*wear") AND ((Orderlines.[Sub Dept]) Not
Like "youth*"));

This returns approx 30k records

2) to return all records of those who have ordered excluding menswear/
womenswear/ footwear and not subdept youth
SELECT Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], Count(UsersWithOrders.g_user_id) AS
CountOfg_user_id, UsersWithOrders.salutation, UsersWithOrders.firstname,
UsersWithOrders.lastname
FROM UsersWithOrders INNER JOIN (Orders INNER JOIN Orderlines ON
Orders.OrderGroup_id = Orderlines.[Order ID]) ON
UsersWithOrders.ordergroup_id = Orders.OrderGroup_id
GROUP BY Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], UsersWithOrders.salutation,
UsersWithOrders.firstname, UsersWithOrders.lastname
HAVING (((Orderlines.Dept) Not Like "*wear") AND ((Orderlines.[Sub Dept])
Not Like "youth*"))
ORDER BY Count(UsersWithOrders.g_user_id) DESC;

This return approx 47.5k records

I am puzzled that my total users with orders is 45k yet the query (2) above
returns more than that. I am aware that a user could order more than one
type of product however I thought that by excluding certain depts it would
not count the user even if they had ordered some other items.
However I think I have just answered my own question! A user can order many
items in one order. As I am looking at the orderlines and not the order, I
am not going to get a distinct result.
I guess what I need to do is append both these results together into a new
table and then de-dupe it? Unless there is a better way of doing it?
Thanks

 

[Jeff Boyce]

Not sure I completely follow, but it looks to me like you have to repeat the
user name/address/etc info in every UserWithOrder record.

A more normalized design would be:

tblUser (info about user)
...

tblOrder
OrderID
UserID
OrderDate
... (info about Order)

trelOrderDetail
OrderDetailID
ProductID
Quantity
Color
Size
... (info about Product ordered)

plus the "support" tables of Product, Size, ...

With a data structure like this, you can look up Orders by customers using
the Order table.

Good luck

Jeff Boyce
<Access MVP>

OK, sure - didn't want to put too much on the email ;-) here we go:

tblUsersWithOrders (45k records):
g_user_id, ordergroup_id, order_number, salutation, firstname, lastname,
Gender, u_dateOfBirth, Shipping_FirstName, Shipping_LastName,
Shipping_AddressLine1, Shipping_AddressLine2, Shipping_AddressLine3,
Shipping_Town, Shipping_County, Shipping_Postcode, Shipping_Country,
Billing_FirstName, Billing_LastName, Billing_Address Line1,
Billing_AddressLine2, Billing_AddressLine3, Billing_Town ,Billing_County,
Billing_Postcode, Billing_Country,

tblOrders (65k records)
OrderGroup_id, order_number, order status, Order Date, Catalogue, Payment,
Total Ex Shipping, Total Inc Shipping, Shipping

tblOrderLines (130k records)
Order ID, Catalogue, Product ID, Variant ID, Description, Dept, Sub Dept,
Qty, total per item, cost price

tblUsersWithOrders has a primary key ordergroup_id which is linked to
tblOrders.OrderGroup_id which is in turn linked to tblOrderlines.Order ID

My queries are:
1) to return all records from dept footwear, menswear and womenswear but not
to include subdept youth (we have youth as a subdept and youthfootwear)
SELECT Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], Count(UsersWithOrders.g_user_id) AS
CountOfg_user_id, UsersWithOrders.salutation, UsersWithOrders.firstname,
UsersWithOrders.lastname
FROM UsersWithOrders INNER JOIN (Orders INNER JOIN Orderlines ON
Orders.OrderGroup_id = Orderlines.[Order ID]) ON
UsersWithOrders.ordergroup_id = Orders.OrderGroup_id
GROUP BY Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], UsersWithOrders.salutation,
UsersWithOrders.firstname, UsersWithOrders.lastname
HAVING (((Orderlines.Dept) Like "*wear") AND ((Orderlines.[Sub Dept]) Not
Like "youth*"));

This returns approx 30k records

2) to return all records of those who have ordered excluding menswear/
womenswear/ footwear and not subdept youth
SELECT Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], Count(UsersWithOrders.g_user_id) AS
CountOfg_user_id, UsersWithOrders.salutation, UsersWithOrders.firstname,
UsersWithOrders.lastname
FROM UsersWithOrders INNER JOIN (Orders INNER JOIN Orderlines ON
Orders.OrderGroup_id = Orderlines.[Order ID]) ON
UsersWithOrders.ordergroup_id = Orders.OrderGroup_id
GROUP BY Orderlines.Dept, Orderlines.[Sub Dept], Orders.[Order Date],
Orders.[Total Ex Shipping], UsersWithOrders.salutation,
UsersWithOrders.firstname, UsersWithOrders.lastname
HAVING (((Orderlines.Dept) Not Like "*wear") AND ((Orderlines.[Sub Dept])
Not Like "youth*"))
ORDER BY Count(UsersWithOrders.g_user_id) DESC;

This return approx 47.5k records

I am puzzled that my total users with orders is 45k yet the query (2) above
returns more than that. I am aware that a user could order more than one
type of product however I thought that by excluding certain depts it would
not count the user even if they had ordered some other items.
However I think I have just answered my own question! A user can order many
items in one order. As I am looking at the orderlines and not the order, I
am not going to get a distinct result.
I guess what I need to do is append both these results together into a new
table and then de-dupe it? Unless there is a better way of doing it?
Thanks

We can't see your tables, so we don't know what you are recording in them.
A query, any query, is (generally) based on underlying data.

How are your tables related, what data is stored in them, what is the

SQL

of
your queries?

--
More info, please ...

Jeff Boyce
<Access MVP>