Need a query with join

[DavidWrightSr]

I have a simple database with 2 tables. a) Florist, a list of purchasers of items and b) Fldetail, the lists of items purchased by one or more of the purchasers.

The Main form is called Invoice and contains all of the purchaser information as well as various text fields with some static information. The SubForm ReportFldetail contains information about individual purchases for each
purhaser.

Queries are created on both Florist and Fldetail to fill in the main form and the subform. The two forms are linked with an ID field.

So far, everything works perfectly, and all of the information I want is presented correctly for each purchaser.


The problem that I have been unable to solve is that the report gives a record for every purchaser in the database including those who have not purchased anything. I have tried everything I have come across to
eliminate these unwanted records, but have had no luck.

I am thinking that the solution could be done by creating the combined tables with a join, but have not been able to do anything with this.

Any suggestions would be greatly appreciated.

David Wright Sr.

[ssanfu]

If I understand correctly, your tables are like this (I know I am missing a lot of fields):

In the query design, double click on the joining line and select option 3:
"Include All records from 'Fldetail' and only those records from 'Florist' where the joined fields are equal"

The SQL will look something like:

SELECT Florist.PurchaserName, Fldetail.purchaseItem
FROM Florist RIGHT JOIN Fldetail ON Florist.FloristID_PK = Fldetail.FloristID_FK;

[DavidWrightSr]

Thanks. I was able to fix the output by using and modifying your select statement once for the main form and once for the subform. Worked beautifully.

David Wright Sr.

[ssanfu]

Excellent! Good luck with your project....