Updating Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

benton - 29 Nov 2005 12:46 GMT

I have a table that links salesman to 12 months of commission he made. when I
run a query for 12 months of the year I realise that there are missing months
for some salesmen. It’s simply because they didn’t make a commission in those
missing months .And therefore those months in which they didn’t produce were
eliminated .Now this is giving me some problems. Is there a way I can run a
query to UPDATE my table with rows of months that were eliminated. NOT ALL
THE SALES MAN HAVE MISSING MONTHS BECAUSE SOME OF THEM PRODUCED monthly
Here’s my table structure.

S/Man/No Month Sales
21010 01/01/2005 $234
21010 02/01/2005 $200
21010 03/ 01/2005 $ 75
21010 07/ 01/2005 $0

April up to June is missing on my table. It’s because those salesman didn’t
bring in any commission at ALL .I can do this manually. But it will take me
many days because there’s about 100 different SalesMan Numbers on my table

Reply to this Message

SEAN DI''''ANNO - 29 Nov 2005 14:01 GMT

Hi ya,

I am not that good at Accsss which you might assertain from the number of
questions I have had to ask and still ask but...just some simple logic for
you to consider which might be useful.

Why not link your query to another query or table which has all salesman and
have a (include all from All Salesman Query). That way you will include
results even if they had no commission. In the same way you could have a
dummy " "alue for each month to pad out the results in case of gaps. This
might help your cause.

> I have a table that links salesman to 12 months of commission he made. when I
> run a query for 12 months of the year I realise that there are missing months
[quoted text clipped - 14 lines]
> bring in any commission at ALL .I can do this manually. But it will take me
> many days because there’s about 100 different SalesMan Numbers on my table

Reply to this Message

Duane Hookom - 29 Nov 2005 15:34 GMT

I would never add dummy records to a table...

I have a table tblNums with a single numeric field Num and values 1 through
some big number. You can use a table like this to create a recordset of all
months:
==qselCurYrMths==========
SELECT DateSerial(Year(Date()),[Num],1) AS MthOf
FROM tblNums
WHERE Num Between 1 And 12;

You can combine this query with a table/query of each unique SManNo to get
every possible combination (Cartesian query). Use this latest query with a
Right Join to the [table with no name given] to get the sales commissions
for each month for each salesman.

Your other option is to fire salesmen who don't produce every month.

Signature

Duane Hookom
MS Access MVP
--

> Hi ya,
>
[quoted text clipped - 35 lines]
>> many days because there's about 100 different SalesMan Numbers on my
>> table

Reply to this Message

Vincent Johns - 30 Nov 2005 09:07 GMT

Here's another way that you may find easier. Assuming you start with
these data (and I added a salesman to make it more interesting):

[Sales] Table Datasheet View:

S/Man/No Month Sales
-------- -------- --------
21010 1/1/2005 $234.00
21010 2/1/2005 $200.00
21010 3/1/2005 $75.00
21010 7/1/2005 $0.00
21030 3/1/2005 $100.00

you can run the Crosstab Query Wizard to generate a Query to display
your results.

[Sales_Crosstab] SQL produced by the Crosstab Query Wizard:

TRANSFORM Sum(Sales.Sales) AS SumOfSales
SELECT Sales.[S/Man/No],
Sum(Sales.Sales) AS [Total Of Sales]
FROM Sales
GROUP BY Sales.[S/Man/No]
PIVOT Format([Month],"mmm")
In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");

... but, to save space, I manually edited the last lines to read

PIVOT Format([Month],"mmm")
In ("Jan","Feb","Mar","Apr","May","Jun","Jul");

This produces the following results:

[Sales_Crosstab] Query Datasheet View (omitting columns after July):

S/Man/No Total Of Jan Feb Mar Apr May Jun Jul
Sales
-------- -------- ------- ------- ------- --- --- --- -----
21010 $509.00 $234.00 $200.00 $75.00 $0.00
21030 $100.00 $100.00

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

> I would never add dummy records to a table...
>
[quoted text clipped - 12 lines]
>
> Your other option is to fire salesmen who don't produce every month.

[...]
benton via AccessMonster.com wrote:

>> I have a table that links salesman to 12 months of commission he made. when I
>> run a query for 12 months of the year I realise that there are missing months
[quoted text clipped - 14 lines]
>> bring in any commission at ALL .I can do this manually. But it will take me
>> many days because there’s about 100 different SalesMan Numbers on my table

Reply to this Message