Stuck on Access relationships. Need help pls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stuck on Access relationships. Need help pls

Author

Topic

planetpiers
Starting Member

1 Post

Posted - 2009-10-22 : 13:52:11

Hi all,

I am relatively new to databases and SQL and i am currently working on a small project using access and asp. So it has to be said i am fumbling around trying to make sense of what i have infront of me.

Most of the site i have created and works as it should, however i just encountered a problem for the life of me i cannot crack. In short i have a page that features two drop downs and free text search field. I want people to be able to edit existing product details and to find the products but they need to search for them by:

Selecting a brand (jumpTo drop down)

Selecting a product size (drop down is populated with sizes currently in use by a specific brand as selected above)

Search text box (refine their search)

I am trying to use a query to retrieve all the product sizes in use by a particular brand to narrow down the search, but every attempt to create a query to do this ends in retrieving all of the sizes for each brand, so i get loads of sizes and not 1 of each different one.

here is a screenie of my database relationships

i keep thinking that they are not correct. Please could someone point me in the right direction as i am loosing hair FAST!

Many thanks in advance

ANY HELP GREATLY APPRECIATED

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 10:16:38

You should probably post this in the Access Forum
But sounds like something like this should work. If not re-post in the Access Forum.


select b.brand
       ,p.Description
       ,s.[Unit Size]
from db_products p
inner join db_brands b
       on b.id = p.db_brands_id
inner join db_availability a
       on a.ID = p.db_availability_id
inner join db_unit_size s
       on s.ID = p.db_unit_size_id
where  a.Availability = '<available value>'
and    b.Brand = '<Selected Brand>'

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources