Access Query help, find unmatched by date

[arcanecantrips]

Hello, I'm new to Access and I've been trying to get my sales team's information gathered in a database.

I have a table that has all my employees names and each date they submit a sale, what I'm trying to do is create a query that will tell me which persons did not submit information for a specific date. As I am needing them submit information daily this is too time consuming for me to pick through each day/employee individually.

The Tables I have are:
-Employees contact list: this table has each employee's names email address, ph#'s ect..

-Daily Sales table, this table is the one described above, it holds all of their sales by date with their name for each date (a single row w/ name date and sales for each day of a submitted information).

I've been trying to use the unmatched Query Wizard to accomplish what I need but i'm not able to make it work.

Any help or advice on this would be greatly appreciated.

Thanks!

 

[bellman101]

Hi,

You can just go to Query Design.

Highlight "daily sales table" and click 'Add'.

Sounds like you dont need any data from any other tables, so you can close the 'show table' window now.

You should now see your table as a list of fields. double click "name", "date" and "sales".

They will now appear at the bottom and you can set your criteria.

In the 'Criteria' row and in the 'date' column, Just type the date you want in the correct format (d/m/yy or m/d/yy - MUST use slashes).

Now you have two options.

To see only employees with no sales for that date, just type =0 in the criteria for 'sales'.

Alternatively, to see all employees sorted with the lowest selling at the top, in the row that says 'sort' (or something similar - I'm going from memory!), click the drop down beneath 'sales' and select 'acsending'.

Now click 'Run' (the big red exclamation).

Let me know if it doesn't work, but that should do the trick!

Cheers,
James

 

[arcanecantrips]

Thanks James!

I tried it out but it doesn't seem to work. it keeps coming up with 0 results. I think the issue is that it's not taking into account my master
employee list to show who should have a sale for a specific date. So when I run the Query it has nothing to compare. I'll keep messing around with the query, if i can solve it I'll post my solution. If you have another suggestion, I'd love to here it.

 

[Joe4]

I've been trying to use the unmatched Query Wizard to accomplish what I need but i'm not able to make it work.

Try creating this Unmatched Query Wizard again, and then after the query is created, change the query to "SQL View" and copy and paste your SQL code here.
This will give us an idea of how your tables are structured, and we may be able to edit that query for you to get it to do what you want.

 

[arcanecantrips]

Ok, here's what I've got:

SELECT [ORBT DAILY WORK TOTALS].[User Name], [ORBT DAILY WORK TOTALS].Date
FROM [ORBT DAILY WORK TOTALS] RIGHT JOIN [User by Date Submited] ON ([ORBT DAILY WORK TOTALS].Date = [User by Date Submited].Date) AND ([ORBT DAILY WORK TOTALS].[User Name] = [User by Date Submited].[User Name]);

Seems to be working...kind of.

The Issue now is that it's duplicating everything about 6 times (the amount of day's i've been making people submit info to me).

Sorry, I forgot to mention, I created a Dates Submitted query and a User by date Submited table and used the main table w/ the user by date submited query to make my mismatch.

 

[Joe4]

Are you saying that it is returning a copy of each result six times? One easy way to collapse those down to single records is to replace the beginning of your statement:

SELECT ...

with

SELECT DISTINCT ...