Complicated? Problem

[Joel C R]

I'm trying to build a database where the user only has to access one table. Any modifications to other tables could be done through this "master" table via relationships, value lists (pop up menus) and the like. The reason for this is simple: I'm building a database for use by other members of my lab, and its purpose is to create requisitions (for use through our organization's purchasing scheme). I've mentioned this in another thread, at a stage where the database was being thought of in an entirely wrongheaded fashion, and was much further from conception than it is now. I also failed to elucidate what the database entailed. So, I figured I would give it another shot and describe my current problem as carefully as I can:

My database has (potentially) the following tables.

REQUISTIONS (the "master" table, which pulls on data from all others. The reports are meant to be printed and submitted)

REQUISITIONERS (data about the people making purchases)

ACCOUNTS (data about the account numbers being used)

VENDORS (data about the vendors, although not really necessary)

PRODUCTS (data about the products)

LINE ITEMS (link between products and requisitions)

Right now, I'm using what I term a "relational value list" between the REQUISITIONS table and the REQUISITIONERS on the category of "name". I suppose I should employ a serial numbering format, but for aesthetics, I've stuck with the name. This is a small lab, and names are essentially unique here. So, I have NAMES as the match field between both tables and a value list defined by the NAMES field in the REQUISITIONERS table. Then, I use fields from the REQUISITIONERS table in the records of the master table. That way, you can use a pop up menu to either call up information about requisitioners or insert new records when needed. I don't know if this is considered a 'no no' or not, but it works quite nicely for my purposes.

And thats the limit of my database's functionality (at least with regards to how I wish it to work). ACCOUNTS is supposed to be a two field table, with PRINCIPAL INVESTIGATOR (i.e. Boss) as the primary key/match field and ACCOUNT NUMBER under it. I'm trying to define a value list based on account numbers. This would also be called up by a field in the master table. The PRINCIPAL INVESTIGATOR is a field in the REQUSITIONERS table, and I've linked it to the master table via a lookup (not displayed in the record, so that you can enter data in the parent table via field insert). My problem here is that I cannot spawn new records in the ACCOUNT NUMBERS table this way.

Now it gets more complicated. VENDORS, as it stands, is just a place holder for the VENDORS field. The contact information is irrelevant for these purposes, and I've considered defining a custom value list instead. However, the VENDOR field is crucial, because, due to the format that the organization uses for purchases, you are limited to one vendor per requisition. Therefore, the LINE ITEMS are regulated by the VENDOR. But the real problem begins when I try to use a portal (as suggested) to call up fields from the LINE ITEMS table (catalog number, description, price, quantity, total) of which the first three (catalog number, description, price) are simply lookups/references to the relatively static PRODUCTS table. The reason why this is a problem is because I'm trying to, as mentioned before, have everything done from the master table. So when you begin the database, it is empty, but through the portal from LINE ITEMS, I want to be able to create new records in the PRODUCTS table, should they not exist already. Obviously, the easy solution (for me) would be to simply fill out the PRODUCTS table beforehand and have the users call them up when needed. But catalog numbers and prices change with time, and I'd like my database to reflect that. Furthermore, the vendor is important as an identifier for PRODUCTS because there is the possibility (however small) that two vendors are using the same catalog number for different products.

Anyhow, if anyone could help me with this, that would be great.

Joel