Query from unrelated tables help please

[joave]

Hi:

I have three tables that I need to set up a query for. The first table
contains the users and related data. The second (sales) table contains each
sale for the last year along with the user ID and date of sale (they only
sell one product so that makes it a little easier). There are several user
tables each containing their own list of users for their respective locations
but only one sales table.

The sales table provides a column for whether the sale was made as well as
a column for the lead (a '1' shows a qualified lead in that column and the
same symbol shows a sale in that other respective column). Those two tables
are related by user id. The third table contains goal data for comparison
purposes: the number of days in each month for division purposes and sales
goals for the team in terms of leads and sales per month (in separate
columns).

The query needs to total the sales for the entire group for a particular
month, figure out how many sales per day they had, and compare it to the set
goal from the 3rd table. Now I do have a Query that totals the sales but I
don't know how to compare the data with the 3rd table to see if the team made
their goals. Is it better to set up VBA to do it (if it can) or can Access
SQL do it, and if so, how? A hint on how VBA could do it would also be
helpful if you are leaning in that direction.

Thank you in advance for any help you can provide.

Dave

 

[golfinray]

It would be easier if all 3 had user ID as the key. Then you could hook them
up. With two tables having user id and the third not having it, what would
you join to? Try adding the user id to the third and then union query the
whole bunch together or try making a query in your query grid.

 

[joave]

Thank you for your reply.

It just doesn't make sense to put a user name in that third table because
the table only holds data for comparison purposes. I need a way to make the
comparisons as shown below; it really doesn't have anything to do with the
IDs.

Dave

 

[RD]

Hi:

I have three tables that I need to set up a query for. The first table
contains the users and related data. The second (sales) table contains each
sale for the last year along with the user ID and date of sale (they only
sell one product so that makes it a little easier). There are several user
tables each containing their own list of users for their respective locations
but only one sales table.

The sales table provides a column for whether the sale was made as well as
a column for the lead (a '1' shows a qualified lead in that column and the
same symbol shows a sale in that other respective column). Those two tables
are related by user id. The third table contains goal data for comparison
purposes: the number of days in each month for division purposes and sales
goals for the team in terms of leads and sales per month (in separate
columns).

The query needs to total the sales for the entire group for a particular
month, figure out how many sales per day they had, and compare it to the set
goal from the 3rd table. Now I do have a Query that totals the sales but I
don't know how to compare the data with the 3rd table to see if the team made
their goals. Is it better to set up VBA to do it (if it can) or can Access
SQL do it, and if so, how? A hint on how VBA could do it would also be
helpful if you are leaning in that direction.

Thank you in advance for any help you can provide.

Dave

Hi Dave,

What are the goals related to? Users? Groups? It would help to see
the actual table structure and perhaps what you want the result set to
look like. It may be that you need to hard code the goal values into
the query as calculated fields and not use a table.

Just a couple of thoughts.

RD

 

[joave]

Hey RD,

That might be the solution that I need (hard coded). Here is the monthly
query that I am looking for (essentially):

User ID Name Leads Sales LeadsperDay SalesperDay
LeadsPercentToGoal

I know how to calculate the leads and sales per day with a query:

SELECT Reps.ID, Reps.Name, Count(*) AS Sent, Sum(LeadTable.Entered)*-1 AS
Entered, Sum(LeadTable.Connected)*-1 AS Connected
FROM Reps INNER JOIN LeadTable ON Reps.ID = LeadTable.[Created User Id]
WHERE (((Month([Created Date]))=[Enter Month]) AND ((Year([Created
Date]))=[Enter Year]))
GROUP BY Reps.ID, Reps.Name, Reps.Name
ORDER BY Sum(LeadTable.Entered)*-1 DESC , Sum(LeadTable.Connected)*-1 DESC ,
Count(*) DESC;

This does everything I need except for compare to goals. This is based on
the month, so the query needs to know how many days in the month and what the
various goals are for the month.

Thank you

Dave