COUNTING QUERY FOR ITEMS PLS. HELP

[Haas]

I have the following two tables
1- Companies
ID, Name, ItemID, ItemDescription
(7560 records each company can have as many as items he can provide)
2- Items
ItemID, ItemDescription
(760 items)

I want to know the items linked the companies and filter with items less
than 2 companies.
please help, i need to execute this Query.
Thank you very much in advance.

 

[Tom van Stiphout]

On Wed, 3 Feb 2010 05:21:01 -0800, Haas

Before we do that, it appears you have an incorrect database design.
Difficulty querying often follows. Yours violates an important
database design rule that says that each table should be about one
thing. Your table1 is about companies and items and it should be
split:
tblCompanies
CompanyID autonumber PK
CompanyName required text
....other company field, address perhaps.

tblItems
ItemID autonumber PK
ItemDescription required text uniqueindex
.... other item fields, perhaps unitOfMeasure, packagesize, weight.

So far we have a list of suppliers and a list of items. Each table is
about one thing.
We can recognize that there is a many-to-many relation between these
two tables: each company sells many items, and each item is sold by
many companies.

To indicate who sells what, you create a "junction" table to complete
the M:M relation:
tblSoldBy
CompanyID long int required PK
ItemID long int required PK
....possible other fields such as Price.

Open the Relationships window and draw lines between the corresponding
fields, and enforce the relations.

Now your question becomes trivial to answer (hint: a totals query over
the third table). Maybe you can make these changes and take a stab at
it. Post back if you need more help.

-Tom.
Microsoft Access MVP

 

[Haas]

It worked, thank you very much, another Question if possible pls.

I have Date field 02/02/2010 I need to query only the year regardless of the
month.

Thanks you once again for the help.

I appreciate your assistance.

 

[Tom van Stiphout]

On Wed, 3 Feb 2010 08:30:12 -0800, Haas

Use the Year function. Something like:
select * from myTable
where Year(myDateField) = 2010

-Tom.
Microsoft Access MVP