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.