UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Duplicate In An Unmatched Query, Access 2016    
 
   
snyder2024
post Jun 17 2019, 06:56 PM
Post#1



Posts: 9
Joined: 7-May 19



Hi!

I'm working with a large database that contains all of my accounts and the products in those accounts for one office.

I have another table that contains all of the products and their sales volumes for our entire region, this is run through a query that filters out on how many accounts the product is in then sorts by sales volume.

What I'm looking to do is an unmatched query that would look at each account and return all the products from the "top items" list that is missing from each account.

When I try to do an unmatched query with the entire list, it obviously gives me back only the few items that aren't currently in use at all in the specific office.

Is there a way for me to design the query to return each account individual so I end up with what each account is missing?

If not is there a shortcut to create the query to have individual information for each of my 193 accounts? And then have them put back together so I can have a view for upper level management to see which accounts are missing what all at once?

Thanks!
Go to the top of the page
 
theDBguy
post Jun 17 2019, 07:26 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,722
Joined: 19-June 07
From: SunnySandyEggo


Hi. It might be hard to say what’s possible without seeing what you got. Can you post a demo?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Jun 17 2019, 07:38 PM
Post#3


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


Agree with theDBguy ... it would be good to see what you have now.

However, if I understand your situation correctly ...

1. Create a Cartesian product query (no JOIN) with a list of accounts and a list of products. This will give you a list of all possible account/product combinations.

2. In another query, connect that query (LEFT JOIN) to a list of accounts with actual products, linked by account and product, and display account and product from the first (Cartesian) query in that query.

3. Limit the query in 2. to those with a null value for product.

This should give you a list of accounts with products not in the account.

But it would be much easier, and accurate, to have a db to work with so we can see exactly how things are structured.

Just some general thoughts, if I understood correctly ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
snyder2024
post Jun 17 2019, 07:45 PM
Post#4



Posts: 9
Joined: 7-May 19



I've attached a few pictures and here's a link to a video. https://screencast-o-matic.com/watch/cq1FcGTGyQ

Trying to avoid having to make 193 individual queries for each account to the do an unmatched query off of those.
Attached File(s)
Attached File  Market_Cataloge.PNG ( 131.56K )Number of downloads: 8
Attached File  division_best_sllers.PNG ( 89.93K )Number of downloads: 6
 
Go to the top of the page
 
snyder2024
post Jun 17 2019, 07:49 PM
Post#5



Posts: 9
Joined: 7-May 19



Here is a sample of db with extraneous data removed as well.
This post has been edited by snyder2024: Jun 17 2019, 07:57 PM
Attached File(s)
Attached File  Sample_DB.zip ( 162.06K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Jun 17 2019, 08:07 PM
Post#6


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


Hi: Thanks for the db. But do you have an independent list of all products? And also a list with all accounts as well? That's what is needed to get a list of all possible combinations of accounts and products to compare with actual sales.

I am not sure how this would be derived from the tables you posted, since both look like actual sales records. Please correct me if I am not looking at this correctly.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
snyder2024
post Jun 18 2019, 07:44 AM
Post#7



Posts: 9
Joined: 7-May 19



My bad, totally skipped that step when trimming the DB to provide a sample.

The query "Top Products in Division" is what you're looking for. I need the sales data because I only want to find the top x (using 100 at the moment) products within the division, then figure out which accounts within the actual branch are missing products from that top list. I'm not sure why I'd need a list of all products and all accounts, but if I did I wouldn't be asking you all for help.

Our hierarchy is divison -> branch -> account. So the table market catalogue is all the accounts within one branch. The top products is all products ranked by sales in the division. Not sure if that adds any clarity.

I will be doing a similar project where I will be given a list of promotional items that we need to find what markets they are not in, but I don't have that list yet. Thought if I could get this working then replacing the top items with the specific items would be easier.

I'm pretty sure I'm the only person in my company who's ever even opened access, so trying to learn as I go here. I really appreciate all the help.
This post has been edited by snyder2024: Jun 18 2019, 08:00 AM
Attached File(s)
Attached File  Sample_DB.zip ( 1.44MB )Number of downloads: 2
 
Go to the top of the page
 
MadPiet
post Jun 18 2019, 09:55 AM
Post#8



Posts: 3,171
Joined: 27-February 09



This design needs some help. Mind if I fix it?
Go to the top of the page
 
RJD
post Jun 18 2019, 10:27 AM
Post#9


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


Hi: It looks like the steps I gave you before will work to give you what you want. Rather than me simply solving this for you, let's see if you can work through the steps with some guidance. That would be better for your Access learning process.

First, create a DISTINCT list of your accounts. You can do this by simply using a query with SELECT DISTINCT [Account Name] from your table named [Market Catalog]. You must do this in SQL view as it is unavailable in the design grid. Create the basic select query in design view, then switch to SQL view and add the DISTINCT ... and test.

Second, in another query, use the query above along with the Top Products query with NO JOINs. Choose the Account Name, SKU and Product Name. This will give you a list of all top products within each account. This is called a Cartesian product query.

Third, in yet another query, combine the query above (LEFT JOIN) with the Market Catalog, joined on Account and SKU. Show the Account Name, Product SKU and Product Name from the left query (the one with all the combinations), and add a field SKU from the Market Catalog and limit it to the SKUs that are Null (criteria ... Is Null).

That should give you the list you want, if it all goes well.

Also note that using an object naming convention starting with the object type (e.g. tbl or qry) and having no spaces (e.g. tblMarketCatalog) will serve you better than what you are using. This is not required, but eases your development and avoids possible error conditions in some circumstances.

Give this a try, and let us know how you are coming along.

Oh, and I see Pieter has suggested, correctly as always, a design adjustment. I think this is warranted. For immediate results, you can use my suggestions. But take advantage of Pieter's offer to see where you can improve your design. This will take longer, and will no doubt involve sharing more of your db, but undoubtedly will be well worth the effort in the long run.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
snyder2024
post Jun 18 2019, 11:18 AM
Post#10



Posts: 9
Joined: 7-May 19



Once I started doing it, it made a [censored] a lot more sense. I don't know why I didn't get what you were saying at first, but it certainly looks like it worked.

I've got to hit the road for a few hours, but I've attached the file as it stands currently. Design changes would be welcome.

You guys rock!
Attached File(s)
Attached File  Sample_DB.zip ( 1.41MB )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Jun 18 2019, 11:53 AM
Post#11


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


I'll let Pieter address the design issues, since he offered, and no doubt already has some changes in mind.

Looks like you've got what you need so far. My thought was to sort the last query by account then sku, for easy reference. But that can easily be done in a report, grouping on the account and listing the products (sorted) within the account, if that's how you look at the data. Or you can group by product, then list the accounts (sorted) within the product. Or have both reports if that helps.

No doubt Pieter will return with some design considerations ...

Continued success with your project and with your Access learning.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Jun 18 2019, 12:52 PM
Post#12



Posts: 3,171
Joined: 27-February 09



taking a step back...

You seem to be missing some tables. It looks like the Market Catalog table describes both a Product (missing), and a Market. What thing does Min Quantity describe? Units Sold and Total Sales are aggregates from a Sales table, which is also missing. Is this like "how much product was sold to each market? (Total Sales, Units sold). Reorder quantity can describe either Product or a relationship between Product and Market. How does that work?

What kinds of questions were you trying to ask about your data? because the appropriateness of a design depends on that. Normalization rules help, but without the business process (maybe) and the questions you need to answer from your data, it's not possible to determine how much normalization is enough.

If you take a little time to design your database, querying it to get answers will be quick and painless. The other route is not fun. (that whole one query per product/division etc is a dead giveaway that something is wrong.) But with a few structural changes, you can make this pretty easy.
Go to the top of the page
 
gemmathehusky
post Jun 18 2019, 03:57 PM
Post#13


UtterAccess VIP
Posts: 4,723
Joined: 5-June 07
From: UK


in general to get the missing items you need to

a) get the items you want to check (query)
b) get the items you HAVE (a query)
c) do an unmatched query to find the items in a) that are not in b) (your unmatched query)

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
snyder2024
post Jun 18 2019, 04:16 PM
Post#14



Posts: 9
Joined: 7-May 19



Piet,

The information comes from a report generated by our own program, so I'm simply exporting the data that already exists in tables in our corporate database. Re-order quantity is set by our branches, and is what is sent to each market when the inventory in the market hits the minimum quantity. The minimum + reorder equals what should be in market at capacity, so I was going to tie that back in at some point to analyse if our operators are putting enough of a given product in based on the sales in the division.

There's also a lot of info that I stripped out of these tables that I felt was unnecessary to achieve the goal of identifying the missing products.

I've requested to be given access to source data, but unfortunately that request was denied due to corporate... reasons.
Go to the top of the page
 
MadPiet
post Jun 18 2019, 04:28 PM
Post#15



Posts: 3,171
Joined: 27-February 09



Okay, that makes sense. If this is more than a one-off, I would probably go this route:
1. Create a properly normalized database
2. link to the spreadsheet(s) you get your data from.
3. create queries to "fix" the data (remove duplicates etc) and append them to final tables.
4. create queries to answer your questions.

But if you would only use it once, it's not worth all that effort.
If you design your database according to the "rules"/best practices, getting answers out of it will be infinitely easier.
Go to the top of the page
 
gemmathehusky
post Jun 18 2019, 04:32 PM
Post#16


UtterAccess VIP
Posts: 4,723
Joined: 5-June 07
From: UK


As I say

you need a query/table with just the items you want to test. (a)

you need a unique values query for the customer you want to test, to include the items they HAVE bought. Obviously they will have bought some many tim,es, os it needs ot be a "unique values" query, (b)

Then you do an unmatched query to find the items in (a) that are not in (b) - (query c)


To test ALL customers, the table (a) needs to include the same list for all customers, so you need to effectively force a cross product join. Put all the customers and all the required products in a query without any join, but show the product IDs and the customer IDs in the selected fields. Then you will get all the required products for all customers, so that query c will then do what you want.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
RJD
post Jun 18 2019, 06:07 PM
Post#17


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


Hi Dave: Take a look at the last db the OP attached. Take a look at the query stack, ending with qryTopProductNotInAcct. This was developed using my advice, and gives the OP what he wanted, using procedures (such as the "cross product" or Cartesian product approach) you are suggesting just above, along with the SQL that gets the list where the actual SKUs are Null by account.

Are we missing something here?

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
gemmathehusky
post Jun 19 2019, 06:19 AM
Post#18


UtterAccess VIP
Posts: 4,723
Joined: 5-June 07
From: UK


@RJD.

My bad. I hadn't read everything closely, and thought the OP was still struggling to get there.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
RJD
post Jun 19 2019, 08:40 AM
Post#19


UtterAccess VIP
Posts: 9,733
Joined: 25-October 10
From: Gulf South USA


No problem, Dave. I thought maybe you saw something we were missing. I do agree the OP needs to revisit the db design, but I think the solution found will get things going meanwhile.

Thanks for joining in.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 10:46 PM