Results 1 to 3 of 3
  1. #1
    adjl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    2

    New to Access, developing an inventory, trying to incorporate relationships.

    Hi all,

    I'm developing a chemical inventory for a university, with pretty basic searching features (users can search by name, vendor, that sort of thing, using a query with parameters that reference the search form fields). The basics are there, but I'm also trying to put in a tendering report that will automatically compare the quantities on site to the amounts needed, and generate a report of what the department needs so vendors can give quotes. That's simple enough to do on its own, but my supervisor has suggested that the "Required Amounts" field should be expanded on so people can search by course number, allowing department chairs to look at just how much each course is using. That will entail having a separate table with the course numbers, chemical names, and required amounts, but I'm not entirely sure how best to set up the relationship to that table. Here are the issues I need to work around:



    -Currently, the database has one table. I don't think there's any way to add this functionality without another table, but I'm not familiar enough with relationships to pull this off easily.

    -In the database, each chemical has several different entries, as even within any given room, there are bottles from different vendors, of different sizes, with different received dates, etc. Because those are segregated, I figured it'd be best to use CAS Numbers as a unique identifier, rather than going through the effort of manually making up my own ID's for every chemical, and I think linking the two tables by CAS number will probably be best.

    -Currently, I've put together a many-many relationship (linking through a junction table that I've filled with indexed CAS numbers by use of an append query). I can get a report to show the summed requirements, and they are associated with the right CAS numbers, but the query is only returning results that do have values in the "Required Amounts" field, regardless of what I search for. Since only a subset of the data will have values in that field (it's primarily for teaching labs, while the inventory covers research labs as well), I need it to return null values as well.

    -If possible, I'd like to be able to concatenate all of the course names that use a given chemical into a single cell on the report. Alternatively, would it be possible to have a button in that cell open a window that would display a list of course names (I know the design behind such a window would be quite simple, but I'm not sure if it's possible to limit the results to the chemical the button's listed with)?


    Hopefully that covers everything, though I can provide more information if needed.

  2. #2
    rzw0wr is offline I will always be a newbie
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Indiana
    Posts
    479
    Chances are, from what I am reading, that you could use 2 more tables.
    One for Vendors and one for the chemical information.
    And possibly one for location.

    Anytime you are storing data more than one time you likely need a table for that data.

    -Currently, the database has one table
    Currently, I've put together a many-many relationship (linking through a junction table that I've filled with indexed CAS numbers by use of an append query)
    This is confusing. How can you have ANY relationships if you only have 1 table?

    I would suggest you get the your tables normalized and the reports/forms will be easier to work with.

    Dale

  3. #3
    adjl is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    2
    Quote Originally Posted by rzw0wr View Post
    Chances are, from what I am reading, that you could use 2 more tables.
    One for Vendors and one for the chemical information.
    And possibly one for location.

    Anytime you are storing data more than one time you likely need a table for that data.
    I considered having more tables, and it'd probably be optimal to do so, but where the bulk of the initial data entry is being done by importing Excel sheets (and that's still ongoing), that limits how much structure I can put in now. That, and where I've got everything else working quite nicely with a single table, I'm happy with it as is. I may normalize everything somewhere down the line if it turns out that the big table causes performance issues once all the data is in, but for now, I think it's alright, unless you feel that such restructuring will be necessary to add this feature.

    This is confusing. How can you have ANY relationships if you only have 1 table?
    That is confusing. Bad wording on my part. The current functional version of the database has a single table, with all of the information in it. The current copy I'm messing around with to try and sort out this new feature has multiple tables, and it's there that I've got that relationship established. I should have clarified that better.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 46
    Last Post: 05-16-2013, 09:53 PM
  2. Replies: 5
    Last Post: 05-03-2013, 08:00 AM
  3. Replies: 2
    Last Post: 12-14-2012, 02:53 PM
  4. Replies: 1
    Last Post: 02-08-2012, 01:33 PM
  5. Developing a complex module
    By Alex Motilal in forum Modules
    Replies: 4
    Last Post: 09-01-2011, 10:03 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