I am attempting to create an automated median database. They put in the parameters and out comes the median.
Basically, I have created a combo box in form [frm input] that will look at the fields of table [data]. I need to do this because the table fields change with each input of data. One time it will be sales next time quantity shipped.
Now, I have two issues.
The first is creating a make table query that takes the fields selected in [frm input] from table [data] and put in table [query data]. Each field has to remain constant each time the make table is ran. So, I added my own names. I attempted to run it, but it doesn't recognize anything.
Here is an example: Object: [frm Input]![Combo4]
Than I realize it isn't pulling data from the table [data] even if I have [data] pulled into the query in the show table section.
I don't know what to do.
The second issues is once I have that table created I need to insert a field in the beginning of the new table [query data] named "Rank" with data type 'Autonumber'. Remember, this must be automated, and I can't open the database each time to add this field. I can't find a way to automate an insert field in form after data is in it.
ok, not quite sure what you're doing, but here's a few things I picked up.
1. Using DAO objects and collections, you can create tables and add fields. There is a lot of info on how to do this in the help files.
2. If you are trying to find the median value of a set of data, you don't need to use temporary tables - use recordsets. Load the desired data into a recordset, move directly to the last value to get the accurate count, and then move back the appropiate number of records to land on the median. Structure your SQL using the "DISTINCT" command to get a list of unique values if you want the numeric median.
3. Another option would be to use Excel, which has a median function. Reference Excel through code (hidden), pass it the array of values and have Excel calculate the median.