Results 1 to 13 of 13
  1. #1
    Join Date
    May 2005
    Posts
    7

    Unanswered: First time user - help required

    I have two tables Artist and Painting, where an Artist can have many Paintings. When adding a new record to another table say for example Purchase; I can select Artist name via a drop down combo box, and select the Painting name via a drop down combo box too. However I would like to only select only those Paintings that belong to that Artist. Can this be done in MS Access? And what would the SQL query look like?

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    OMFG!!!! Well what are your table layouts? This is a stupendously easy thing to do depending on how you've designed your tables ... A rather simple query ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2005
    Posts
    7
    Lol, I know this is simple but I can't get it to work in access and it's getting on my nerves now! Database is attached and the table I'm trying to work on is Purchased, at the moment PaintingID is displaying all the records.

    Thanks M Owen
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by rraghvani
    Lol, I know this is simple but I can't get it to work in access and it's getting on my nerves now! Database is attached and the table I'm trying to work on is Purchased, at the moment PaintingID is displaying all the records.

    Thanks M Owen
    I can't download files here. Slap your table designs (not all of it either ... Just the revelant parts) in a reply ... Generally:

    SELECT FieldX, FieldY, FieldZ, ... FROM SomeTable INNER JOIN SomeOtherTable ON SomeTable.PrimaryKey=SomeOtherTable.ForeignKey WHERE (SomeTable.PrimaryKey=SomeCondition);
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    May 2005
    Posts
    7
    At the moment it's like, which looks similar to yours.

    SELECT Paintings.Painting
    FROM Artist INNER JOIN Paintings ON Artist.ArtistID = Paintings.ArtistID
    WHERE (((Paintings.ArtistID)=[Artist].[ArtistID]));

  6. #6
    Join Date
    May 2005
    Posts
    7
    Relationship diagram RE.bmp

  7. #7
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33
    Quote Originally Posted by rraghvani
    At the moment it's like, which looks similar to yours.

    SELECT Paintings.Painting
    FROM Artist INNER JOIN Paintings ON Artist.ArtistID = Paintings.ArtistID
    WHERE (((Paintings.ArtistID)=[Artist].[ArtistID]));



    Try a different condition in your WHERE statement, because you're FROM and WHERE statements are essentially the same which might be causing your problem. For example, if you have the artistID displayed on your form you could do...
    SELECT Paintings.Painting
    FROM Artist INNER JOIN Paintings ON Artist.ArtistID = Paintings.ArtistID
    WHERE (((Paintings.ArtistID)=" & Me!ArtistID & "));

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by rraghvani
    Relationship diagram RE.bmp
    Is this homework (during the summer hols !!) or a real situation ?

    This is slightly off topic to the question you asked but just a couple of comments on your relationship diagram...
    • There should be no relationship between Purchased and Artist (unless you really do mean a customer purchased an artist). The references to artist from purchased can always be looked up via the relationships of each to paintings.
    • It appears you have not defined a primary key in either your Customer table or your Purchased table. This is mandatory for relational database design.
    • The relationships between Paintings and Purchased, and Purchased and Customer should be one-to-many. They have probably defaulted to the undefined relationship you see because you have not set the primary keys mentioned above. I suggest you delete them, add the relevant primary keys, then re-implement the relationships.


    Chris

  9. #9
    Join Date
    May 2005
    Posts
    7
    * I want there to be a relationship between artist and painting, because an artist can have many paintings.
    * Primary key has been defined for Customer.CustomerID but not on Purchased.
    * I'll fiddle around with that.

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by rraghvani
    * I want there to be a relationship between artist and painting, because an artist can have many paintings.
    Yes I totally agree with that. But in your diagram you have also created a direct relationship between Artist and Purchased. You don't need it. You can also remove ArtistID from the Purchased table - it has no relevance. If, at some point, you want to list all purchases of paintings by a particular artist that you can do this with an appropriate join query.

    If you choose to leave it in then someone could create a purchase for a painting and enter an entirely different artist by mistake. Hence, as well as being not required, it will create issues in managing your database.

    Chris

  11. #11
    Join Date
    May 2005
    Posts
    7
    I understand, but I would also like some sort of validation where user selects artist and then selects only those paintings from that artist and visa versa. I remember doing this in Paradox about 8 years ago! Can it be done in Access? And how can I do it as I'm not familiar with Access. I’ve attached my sample database in the 3rd reply, I would be grateful if you could make the appropriate changes and attach the modified database so that I can see what exactly has been changed.

    Thanks in advance. Have a good weekend.

  12. #12
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by rraghvani
    I understand, but I would also like some sort of validation where user selects artist and then selects only those paintings from that artist and visa versa. I remember doing this in Paradox about 8 years ago! Can it be done in Access? And how can I do it as I'm not familiar with Access. I’ve attached my sample database in the 3rd reply, I would be grateful if you could make the appropriate changes and attach the modified database so that I can see what exactly has been changed.

    Thanks in advance. Have a good weekend.
    Attachment ?

    As Mike mentioned, you can do pretty much most things in Access with the right design.

    You mention the word validate but looking at your original question I'm guessing what you really what to do is have a purchase entry screen where the user can select an artist and then is presented with the available paintings by that artist to choose from. This topic probably appears in about 1 in 20 threads at a guess so it's a common question and is about selection criterion and user interface rather than validation imho.

    First of all, you must not store artist in your Purchased table. That doesn't mean you can't have a dropdown allowing you to select an artist then painting. The way to do this is use an unbound combo to select the artist and a bit of code that changes the options in the picture combo once an artist is selected.

    Chris

  13. #13
    Join Date
    May 2005
    Posts
    7
    I've managed to resolve the problem via adding some VB code. Thanks for you help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •