data from 2 drop down menus

[ispfemme via AccessMonster.com]

I have been working on a database that would allow a user to select a
supplier name from a drop down combo box, then select the quarter from
another drop down, and input 3 values that would generate a percentage for
them immediately. My approach was to create separate tables for each quarter
and then pull from each by using a query that would link all the quarters as
defined by the supplier name. I wanted ot know if anyone had any ideas on
how to organize the data so that the drop down menus can be used for both the
Supplier name and the Quarter. I had it set up before so that the quarters
were separated on the form but the idea was rejected. Any ideas are greatly
appreciated.

 

[KARL DEWEY]

My approach was to create separate tables for each quarter and then pull
from each by using a query that would link all the quarters as defined by the
supplier name.
I am confused as to what you are trying to do. To me a 'quarter' is a
fraction of a year, a three month period.
How are you trying to relate a three month calendar span to a supplier?

 

[ispfemme via AccessMonster.com]

Yes. So we have a supplier rating system based on quality lots. This
originally started off as an excel spreadsheet that had fields for:

Supplier Name, PreviousYear%, 1stQuarter-Lots, 1stQuarter-Rejected ,
1stQuarter-Accepted% , 1stQuarter-PW , 2ndQuarter-Lots, 2ndQuarter-Rejected
, 2ndQuarter-Accepted% , 2ndQuarter-PW , 3rdQuarter-Lots, 3rdQuarter-
Rejected , 3rdQuarter-Accepted% , 3rdQuarter-PW , 4thQuarter-Lots,
4thQuarter-Rejected , 4thQuarter-Accepted% , 4thQuarter-PW , YearToDate-
Lots , YearToDate-Rejected , YearToDate-Accepted%


So for each Supplier, it is now expected that I give the user a drop down
menu to select the Supplier needed. From there, the Quarter should be a drop
down menu as well that would allow a user to input the Lots, Rejected, and PW
fields (the accepted % is to be calculated after the lots and rejected fields
are entered). After each quarter is done, the text boxes I have on the other
side of the form should update the yeartodate values based on the most
current quarter entered.

I started off by setting up separate tables for each quarter, PreviousYear%,
and then the YearToDate values as well. This total 6 tables in all, each one
containing the Supplier name field as well as their value fields. I set up
the Supplier names as the primary key for all of the fields so I was able to
later on generate a new table from pulling all the values into a query.

Now I need to have the form set up and I'm not sure how to get it to link up
with the fields I need. I was hoping to create a subform that would contain
the drop down list for the quarters (the main form would have the supplier
name linked up) and then after the Lots, Rejected, and PW values were entered,
the Accepted% and the yeartodate values would be automatically updated.

from each by using a query that would link all the quarters as defined by the
supplier name.
I am confused as to what you are trying to do. To me a 'quarter' is a
fraction of a year, a three month period.
How are you trying to relate a three month calendar span to a supplier?

I have been working on a database that would allow a user to select a
supplier name from a drop down combo box, then select the quarter from

[quoted text clipped - 6 lines]

were separated on the form but the idea was rejected. Any ideas are greatly
appreciated.

 

[KARL DEWEY]

I recommend you reduce the number of tables and not store calculated values.
Just calculate them as needed.
Use this table structure ---
Supplier_Name - Text
ActionDate - DateTime
Lots - Number - Integer
Rejected - Integer
PW -

--
KARL DEWEY
Build a little - Test a little

Yes. So we have a supplier rating system based on quality lots. This
originally started off as an excel spreadsheet that had fields for:

Supplier Name, PreviousYear%, 1stQuarter-Lots, 1stQuarter-Rejected ,
1stQuarter-Accepted% , 1stQuarter-PW , 2ndQuarter-Lots, 2ndQuarter-Rejected
, 2ndQuarter-Accepted% , 2ndQuarter-PW , 3rdQuarter-Lots, 3rdQuarter-
Rejected , 3rdQuarter-Accepted% , 3rdQuarter-PW , 4thQuarter-Lots,
4thQuarter-Rejected , 4thQuarter-Accepted% , 4thQuarter-PW , YearToDate-
Lots , YearToDate-Rejected , YearToDate-Accepted%


So for each Supplier, it is now expected that I give the user a drop down
menu to select the Supplier needed. From there, the Quarter should be a drop
down menu as well that would allow a user to input the Lots, Rejected, and PW
fields (the accepted % is to be calculated after the lots and rejected fields
are entered). After each quarter is done, the text boxes I have on the other
side of the form should update the yeartodate values based on the most
current quarter entered.

I started off by setting up separate tables for each quarter, PreviousYear%,
and then the YearToDate values as well. This total 6 tables in all, each one
containing the Supplier name field as well as their value fields. I set up
the Supplier names as the primary key for all of the fields so I was able to
later on generate a new table from pulling all the values into a query.

Now I need to have the form set up and I'm not sure how to get it to link up
with the fields I need. I was hoping to create a subform that would contain
the drop down list for the quarters (the main form would have the supplier
name linked up) and then after the Lots, Rejected, and PW values were entered,
the Accepted% and the yeartodate values would be automatically updated.

My approach was to create separate tables for each quarter and then pull

from each by using a query that would link all the quarters as defined by the
supplier name.
I am confused as to what you are trying to do. To me a 'quarter' is a
fraction of a year, a three month period.
How are you trying to relate a three month calendar span to a supplier?

I have been working on a database that would allow a user to select a
supplier name from a drop down combo box, then select the quarter from

[quoted text clipped - 6 lines]

were separated on the form but the idea was rejected. Any ideas are greatly
appreciated.