Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    JJAMII is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    11

    How to count the number of specific values in a field, then display the result in another field.


    Hi

    I am creating a database for a games rental system. I have one table which is titled 'Games' and another which is titled 'Game Copies'. To add a Game Copy, you must first add the Game in to the Games table. This will mean entering a title etc. When you then go to add a game copy, there is a combo box which will drop down and show all the current titles from the Games table so that you can select the game you want and add a copy of it to the Copies table.

    In the Games table, there is an attribute for 'Copies Held'. Is there some way that i can have the database count how many copies of the Game are in the 'Game Copies' table, and display it in this field?

    Thanks, Jamie.

  2. #2
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    You can create a field (let's say totalCopies) in your Game Copies table that sums the # of copies, then create an identical field in your Games table and set a 1:1 relationship between the two fields. I have been away from access for the last 4 months due to school so there may be a better way but this is my initial solution.

  3. #3
    JJAMII is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    11
    The problem there is that there is already a One to Many relationship from Game, to Game Copies.

  4. #4
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    I am having a hard time visualizing your table(s) setup. Can you post a screenshot of the relationships screen? I am wondering if it would be better to have your Game Copies table consist of a PK of the game title then a field numCopies that can be incremented when another copy is added to the system.

  5. #5
    JJAMII is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    11
    I am actually having a pretty hard time with the database as a whole. It's for an assignment at university. We were given sets of data to normalize and drew up ERDs based on the normalization process. My ERD is almost identical to the one the lecturer uploaded as an example of how it should be, so that is apparently correct. But now that i'm trying to implement the planning into a database the structure just doesn't seem to make sense.

    But yeah anyway, sorry to ramble, here are the relationships and my final ERD.




    Click image for larger version. 

Name:	Relationships..png 
Views:	14 
Size:	149.3 KB 
ID:	23267
    Click image for larger version. 

Name:	ERD.png 
Views:	13 
Size:	28.6 KB 
ID:	23268

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why do you have CurrentRental and RentalHistory tables? Should be 1 table - Rentals.

    I would not advise 'incrementing' a field. Saving aggregate data is usually a bad design. Enter raw data and calculate summary data when needed. CopiesHeld should be a calculated, not stored, value.

    Title should not be in both Games and GameCopy, only in Games.

    Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JJAMII is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    11
    Yeah i wasn't sure about Rental History / Current Rental. I just added the Current Rental table not long ago as i was struggling to see how i would be able to issue rentals without it. The attributes in the Current Rental table were in the Game Copies table before this. But then the database is also required to record rentals history, which is why that table is also there.

    I tried to set CopiesHeld as a calculated value, but from what i could see, the only calculations i could make were using values from within the same table.

    And ok thanks i will remove Title from GameCopy and sort out the naming convention.

    Thanks again and apologies, it's a bit of a mess and i'm a little lost so trying to assist me will be quite a hassle i imagine!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    CopiesHeld would be calculated with aggregate (GROUP BY) Totals query that counts the records in GameCopy with grouping on the CatalogueNo or with DCount() domain aggregate function.

    Rentals should allow multiple records for each member. This table would be used to show if each game copy is currently rented and where as well as to summarize members rentals. This is what queries and reports are for - data manipulation.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    You can use a query to perform the operation of calculated field for CopiesHeld. A query will be able to access values from multiple tables and essentially place them into one 'table'.

  10. #10
    JJAMII is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    11
    Ok thanks a lot guys.

    June, my current plan involves having a Status attribute in the GameCopies table that can be set as either Available, On loan, or Reserved. There is also a MemberID Attribute in the same table which would be left blank when available, or show the MemberID to whom the copy is on loan to or reserved by. Do you think it would be better to have the attributes such as Date Issued and Return Due, within this same table? And Remove the CurrentRentals table i recently created all together?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Again, ideally, the status and who has the copy should be calculated in query, not saved into table.

    You might find the MS Lending Library template interesting. If I remember correctly, it shows these calculations.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    JJAMII is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    11
    Oh i see, sorry, didn't understand what you meant really the first time. Didn't know queries could be used in that way. Thanks i'll have a look!

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I will qualify my advice to say that saving the copy status is relatively simple and maybe simpler than the query approach but does require code (macro or VBA). The real trick would be figuring out what event to put code into.

    Calculating the copy count should still be done with query or DCount() and not saved to table. Saved aggregate data has risk the value will get 'out of sync' with the raw data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    JJAMII is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    11
    I was going to have an issue rental form, from there i could enter issue date, return due and memberID of the holder, along with changing the status of the copy.

    Also once i have calculated the copy count with a query, could it then be displayed in the CopiesHeld field i mentioned earlier?

    Thanks again, will try to stop asking questions after this! Lol

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This form would be bound to Rentals table. Why would you need to show copy count?

    Could pull the calculated count from the query with DLookup() or just do a DCount() and don't build query. Always more than one way to accomplish.

    Displaying aggregate data on form can be a challenge. Reports are better suited for this.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-04-2015, 03:00 PM
  2. Replies: 1
    Last Post: 06-25-2014, 01:05 PM
  3. Replies: 3
    Last Post: 09-12-2013, 02:18 PM
  4. Replies: 4
    Last Post: 03-01-2013, 11:49 AM
  5. Count of field based on specific values
    By tazzmann67 in forum Access
    Replies: 2
    Last Post: 03-30-2011, 09:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums