[B]UPDATING TABLE[/B]

[thembela]

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

 

[KenReay]

Hi

You could do this with a query

Have a table of Months (tblMonths), containing one record permonth

make a query with the Sales table and the Months Table, no join, so you get all combinations of Salesman and Date (in months table) - a Cartesian Join I think it is called from memory

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dhookom]

Also, check the reply to your same question in the public.Access news groups. I think Ken's and my reply there are basically the same. We both used the term "cartesian" ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

[pedantic]cartesian product or cross join[/pedantic]

 

[thembela]

When I run a crosstab query based on the query created from TblMonths and Salestable with no join. It works but the problem is I get the same value for commission for every months

S/Man/No Month Sales
21010 01/01/2005 $234
21010 02/01/2005 $234
21010 03/01/2005 $234
21010 04/01/2005 $234

 

[spizotfl]

is this more of an outer join issue? do you have a salesman table and a table with commission information? if so, if you do not have an entry in the commission table, then your query won't return a record. i don't know how your tables are setup, but this may be the issue....

 

[dhookom]

thembela,

When I run a crosstab query

Show us your SQL view. When using your Salestable in a query, it must be joined to some other table. Set the Join Properties to include all the records from the other table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[thembela]

This whole thing works .I get the combination of all the Sales Man numbers and dates.But the problem is with the commissions.The commissions fields that had previuosly no value now get populated when i run the query.which gives me wrong amounts.For instance when I run a query.The monthly commission from 07/01/04 to 06/01/05 will be the same value for each salesman.Here's my sql:

SELECT TDates.Num, TblSales.S/ManNo,TblSales.Commisions
FROM TDates.Num, TblSales;

 

[dhookom]

Read my previous post regarding a join. I assume you need to join Num from TDates to a field in tblSales.

Also, I can't imagine your above SQL would not throw an error since you have a field name in your FROM statement.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]