Make-Table Query

[tangoman66]

I want to collate information of a form in a table, is it possible to use a make-table query to do this? If so how do I select the cell of the form would it be in the form...

Forms![My Form]![My Text Box]

Cheers,

 

[Len Boorman]

Do not exactly understand your question. Is the form based upon either a query or a table. ?

If the form data already exists then you can use a make table query or you can manipulate the data within queries. There is no difficulty using a query to query a query. You can further query the results as well.

If you create a table then the data will be static unless you delete and recreate the table each time you think new data may have been added. Using queries means that you always have the latest data


Len B

 

[tangoman66]

The form is based on a query but the bit I want to put in a table is formula that is a combination of IIf, OR and AND statements. It is one BIG formula that will not go in a query as it is too big and makes Access crash. If you want I can post the formulas but you probably won't understand them as they are very specific to the queried data.

Cheers

 

[Rich]

Post it anyway

 

[tangoman66]

Here you go then. ive copied it into word for you. The last one (BIG FORMULA) is the 4th formula down with the first second and third formulas inserted in the appropriate places

 

[Len Boorman]

Okay its a big formula and I dread to think what it is trying to do, so sorry but do not think I can help you.

If it was me because I do not think a formula that big is comprehensible or maintainable I would look for a different way of doing whatever it was I needed to do,

Good Luck


Len

 

[Pat Hartman]

Forms do not store data, tables store data. The tables in a relational database do not have cells. They have rows and columns. Formulas are not written the way they are in a spreadsheet. You'll need to describe your table structure and explain the calculation you want to perform.

 

[tangoman66]

Forms do not store data, tables store data. The tables in a relational database do not have cells. They have rows and columns. Formulas are not written the way they are in a spreadsheet. You'll need to describe your table structure and explain the calculation you want to perform.

I have three tables tblSubject, tblBlock and tblImport. tblImport gets external data from excel and has 8 fields (UPN Number, Forename, Surname, Form, Choice 1, Choice 2, Choice 3, 3 Choices Entered) the 3 choices have to be entered manually they are NOT imported. tblSubject has (Subject ID Number, Subject Name, Technology). tblBlock has (Option Block ID, P1C1, P1C2, P1C3, P1C4, P2C1, P2C2, P2C3, P2C4, P3C1, P3C2, P3C3, P3C4). I have a queries that will produce class lists, class sizes, whether 3 choices entered or not, subject data. I also have a query that shows all the data from tblBlock and all the data from tblImport (this is called qryOption). I have a form that acts as a dialog box where the subject name is selected from the combo and class lists can be queried and reports made. I have a form that uses the data from qryOption and displays P1C1, P1C2 etc. in 3 columns of four, I also display each individual pupils data on the form. I have then added several (36 not quite several!!) text boxes and these use IIf statements to see whether or not the subject chosen by the pupil is in P1C1 or P1C2 or P1C3 etc and if it is then it displays a 1 otherwise 0. This enables me to use other text boxes to count the number of 1's. I then use another text box that uses the fact that if three ones are in the text boxes for each column then three choices can be taken and if only 2 non 0 numbers then 2 choices and if only 1 non 0 number then only 1 choice can be taken from the current grid. What I want to do is use a query to make a table that will contain the number of choices that can be taken so that I can then count the number of people who can take 3 choices, 2 choices, 1 choice etc. This would then enable me to compare the option blocks and find out which block enables the most people to take all three of their choices.

 

[Pat Hartman]

There are numerous problems with your table structure.
1. I cannot descern any relationship between any of the tables.
2. Choice has a 1-many relationship with [UPN Number] and so belongs in a separate table where each choice will be stored as a separate row. There are business rules that limit the number of instances of C to 3.
3. PxCx are completely unintelligible but those fields seem to represent a hierarchial relationship. P has a 1-many relationship with [Option Block ID] and C has a 1-many relationship with P. There are business rules that limit the number of instances of P to 3 and the number of instances of C to 4.
4. Using embedded spaces or special characters in object names will cause problems if you need to use VBA for anything. Also make sure that you avoid the use of function names or property names. I have posted references to lists of reserved words that you should be able to find by searching.

Try to explain your problem in terms of the business requirement rather than in terms of your column names which don't convey any information.

 

[tangoman66]

The school I am building the database for wants (if possible) to generate an option block so that as many people as possible can take their three choices. If this cannot be done I believe it would be best to at least make something useful to help them on their way. I have designed a form that will tell the user how many choices a pupil can take given the layout of the option block. If I could then query the calculation so that I can get Access to count the number of people who can take 1/2/3 choices for a given Block ID it would be useful for then comparing to find the best. If I cannot query these results then I am going to produce a simple report that will have a layout where the user can use a simple tallying method to compare the Block's.

 

[Pat Hartman]

Access can count anything you want it to count but you keep referring to your "solution" rather than to the actual business problem. You have imprinted on a spreadsheet matrix as the only possible solution. That is not the case at all when working with a relational database but I need to understand the problem, not your solution.

Let me take a stab at what I think the problem is. You have 12 classes but only three timeslots. You are trying to decide which three classes should be scheduled to satisfy the maximum number of students. Rather than just counting the requests by class and using that as the selection criteria, you want to count them as a set. So you want to somehow evaluate the following list:
a1, d2, c3
b1, d2, a3
b1, a2, a3
d1, a2, a3

If my analysis is correct, it seems that the user has already decided that 1 from column1, 1 from column2, and 1 from column3 will be offered dispite the fact that three of the 4 choices in column 2 would be hands down winners if you used the straight count most requested method.

 

[tangoman66]

Thats it! The block will eventually look like below...

Period 1 Period 2 Period 3
Class 1 Subject Subject Subject
Class 2 Subject Subject Subject
Class 3 Subject Subject Subject
Class 4 Subject Subject Subject

Where a pupil has to take a subject from each of the 3 periods. I want to find a solution of subjects where the most number of pupils can take their 3 choices. Inevitably some pupils will have to change their choices to fit in with the final block.