Hi,

I have a little problem with Data access page.

I have builted a report on a dap that containe fixed columns.  It look
like this:

CarModel      Dealer1   Dealer2    Dealer3   Total

Sunfire          10       11         7         28
Grand AM         8         7         5         20
etc.


Basicaly the report will give me every day the amout of unit sold by
dealer.  The report will only list the models sold (so if no Grand AM
are Sold that day, the row will not appear) but I want it to always
list the dealers (columns) even If they have no unit sold.  This is
the problem because if there is no data for Dealer3 in the table/query
then the data does not exist and when I try to see the DAP, I get an
error message saying that there is no link or reference for Dealer3
and I am unable to see anyting.  (of course if the table would contain
data for dealer3 then It works.)

Is there a fix for this?  Is there an option saying something like if
there is no reference just display "".  ???

A quick and durty fix would be to create a empty row in my database
but this would be harder as my tables a created daily from a complex
set of macros etc...

Anyone?

Hi Alex,

Assuming that the data is in 2 different tables, you only need to
re-write your query to extract the data in the way you require. The
trick lies within the join type used to perform the query. If your
table names are Dealer and Sales the query:

Select * from Dealer INNER JOIN Sales on Dealer.DealerID =
Sales.DealerID

will give you the results described above. However the query:

Select * from Dealer LEFT JOIN Sales on Dealer.DealerID =
Sales.DealerID

will give you your desired result set. 

Left join tells the database engine to retrieve all the dealers (the
table on the left hand side of the query) and any sales that match.
Inner join tells the engine to get ONLY the dealerID's which are in
both the LEFT and the RIGHT tables.

Please let me know if you need any clarification, or if your join is
more complicated than this, if you specify your table details I can
describe the query exactly for you.

Good luck,

Molloch

---

Request for Answer Clarification by alex421-ga on 30 Aug 2002 06:22 PDT

No, there is only one table si I only use one field for the dealer
(the name), I dont register the adresse, etc.

If you have an e-mail adresse I can send you a simplified version of
the database so you can see.  The data look like this:

Dealer     Make      Model     Color    Motor etc.
Dealer1    Pontiac   sunfire   red      4 cyl.
Dealer1    Buick     century   black    v6
Dealer2    Buick     regal     blue     v6

and so on.   THe description above is a report base on a query that
pulls its data from the table I just wrote.  It is a simple query. (
the complex stuff was to dowload the data and build the related tables
(done by macro during the night) but at this level it is very
simplified so my data access page can be faster.)

---

Clarification of Answer by molloch-ga on 30 Aug 2002 18:28 PDT

Hi,

Unfortunately the only way to get the desired effect is to have 2
tables. All you need to do is to have a second table with each dealer
in it. Just create a dealer table with each dealer in it, and link it
to the table above by using the query specified in the answer, this
will work perfectly for you. You will end up with the following 2
tables:

tblSales
Dealer     Make      Model     Color    Motor etc. 
Dealer1    Pontiac   sunfire   red      4 cyl. 
Dealer1    Buick     century   black    v6 
Dealer2    Buick     regal     blue     v6

tblDealer
Dealer
Dealer1
Dealer2

And the following query:

Select * from tblDealer LEFT JOIN tblSales on tblDealer.Dealer =
tblSales.Dealer

You wont need to change your existing tables at all, just add that
dealer table and change the query. You will need to make sure that
each time a new dealer is added it is placed in the dealer table, but
this shouldn't be too bad.

You could also create a query for Dealer instead of creating a table:

qryDealer
Select DISTINCT Dealer from tblSales

And change the query to:
Select * from qryDealer LEFT JOIN tblSales on qryDealer.Dealer =
tblSales.Dealer.

I would prefer to use the dealer table myself as it gives you more
control over the result set, but you may find the query easier for
now.

Either way will give you your desired result. 

Unfortunatly I cannot give you an email address, but if you still have
problems you can request another clarification and if you can write
out your tables as you have done previously I can work it out from
there. Give the above a try first though, I am sure it will work for
you.

Molloch

Hi Alex,

I would go with the quick and dirty method that you mentioned...it is
actually a LOT easier than the other method I have in mind that
requries the use of temporary tables and can be a real mess for a
non-expert.

You could do this quite simply with a macro that runs after all the
others are complete...it requires a little set theory application but
not overly complicated. If you want me to post this idea as an answer
let me know :)

answerguru-ga

Actually It wont work,

The data access page is based on a query.  This query select the right
date (the current month)and this is based on an ongoing sales table. 
So every month, for the first couples days, it will never work since
some dealers will not have any recorded sales yet.

Quick dirty method is a patch but I am looking for a right way to
build a report.  Furthermore, I want to build another report on a
daily basis.  the query command "=date()" would then be used.  To make
this work with the Quick and dirty way would require me to create a
faque entry for each day and to each dealer.  This does not make any
sense.  I need a tangible solution.