Hi, I try to simplify what I did in Access as follows:



Name Of Company----Jan----Feb----Mac----Apr

------A---------------3-------3------4------5
------B---------------2-------3------3------2
------C---------------5-------6------6------5
------D--------------

Note: No data inserted for Company D for Jan-Apr

Question:

1. If the list of companies are longer (say thousands), how can I make a query to show company that make no transaction in Jan - April ??? AND

2.How can I make a query to show all the companies (with and without transaction) together?

FYI: For Qusetion 2, I can only get the filtered result shown for Company A,B & C while no Company D listed (coz no input/data??). Pls help me, TQ


Max

First of all, you should not have repeating fields (NORMALIZATION NO-NO). You should redesign this so that yu actually CAN get this to work.

Your table structure should be like this:

Company Table
CompanyID - Autonumber (primary key)
CompanyName

Sales table (I used sales for an example as you didn't specify what you were storing)
SalesID - Autonumber (primary key)
CompanyID - Long Integer (foreign key)
SaleDate - Date/Time (short date)
SaleAmount - Double

This table is what I made for storing data:

Company Table
CompanyID-Autonumber(PK)
Company Name
Company Registration No.

Order Table
Date
Volume(m3)
OrderNumber

I wanted all the companies listed by month (with number of ordering[counted from no. of date?] ) . The query result should match my table posted previously where it show Company D with "0" for Jan-Apr.

Pls advice. TQ

Let's check this example:

Company A
Reg. No. : A1234

Company B
Reg. No. : A2345

Company C
Reg. No. : A3456

So, I made Company Table as follows:

Company Table
*Company Name
*Company Reg. No-(PK)

And Sales Table as follow:
*Company Name
*Company Reg. No.
*Order No.-(PK)
*Date
*No.of boxes ordered
------------------------------------

If the sales table being filled for any of the 2 companies above, I can only managed to get that 2 companies when make a query. I wanted to get the list of all three companies so that I can know which company do not make any order in the particular month.

How can I to make that?