Referencing Column names

[Runawaygeek]

Hi Guys,

I have a table that feeds a form, the table is a list of things that could be required for input in to a system based on client request. Lets call it "Detail"

Client ID | Client Name | Profile | Image attached | and so on.

The data in these columns, is not really for anything other than a visual for the user. most are a YES/NO Value or an option from a drop down in another system.

As part of a "Complexity Project", I have added a weight to each of these tasks, so i have another table that for this, "Tasks"

Task ID | Task | Weight

I was going down the idea of

"IIF(ISNULL([DETAIL].[Image attached]),0,[Tasks].[Weight]

But for this to work as such, i would need to link the tables. Obviously, they dont really link. As i am trying to compare Data to headings..

any ideas??

 

[plog]

No, no ideas. Very confusing what you are trying to do. So I focused on this:

As i am trying to compare Data to headings..

Field names should be generic, they should not contain data. If you feel the need to "link" to a field name then you are doing it wrong and instead that field name should be data inside a table, not in the name.

Perhaps you can provide us with sample data that demonstrates your issue better.

 

[stopher]

I'm also confused.

As I read it you want a tick list type thing? In which case instead of having the things to tick as the headers, put them in rows (in one colum) and the tick box in the second column - a bit more how you would expect a tick list to look.

As to joining, you can always join by not assigning a join (a cartesian product). Just add both tables to the query editor but don't join them. This will produce every combination of each row in one table crossed with each row in the other. As an example, if you have 2 rows in one table and 3 in the other, then the query will produce 6 rows. Of course if one table has only one row then only this row will be crossed with the other table.

 

[Runawaygeek]

Hi Stopher

how would you then use that tick list for 100+ clients? They way you have it set out would suggest it can only be ticked once OR you have a column for each client, adding 100s of columns is going to get messy?

You are right, its a tick list type thing, where i have all the Tickables as headings so that each row relates to a clients entries.
Maybe this is the wrong way to hold this data, what would be better?

 

[stopher]

Hi Stopher

how would you then use that tick list for 100+ clients? They way you have it set out would suggest it can only be ticked once OR you have a column for each client, adding 100s of columns is going to get messy?

You are right, its a tick list type thing, where i have all the Tickables as headings so that each row relates to a clients entries.
Maybe this is the wrong way to hold this data, what would be better?

Just add one extra column for ClientID i.e. 3 columns.

 

[Runawaygeek]

So, I would have a table of Tasks, (TASK_ID, TASK NAME) a Table of Clients (Client_ID, Client Name) and then a joined table like, (Result_ID, TASK_ID, Task_Name, VALUE, Client_ID)

That makes sense, why i could not see that before, i dont know!

Just need to workout how i build the form, using the Tasks list, but populate with the Tasks results, I assume some Child_Table link?

Cheers.
B

 

[stopher]

I think I know where you are heading (?)

Yes you could have a form/subform approach where the form links to the client table and the subform links to the client/tasks table. You will need a physical table for client/tasks like this:

ClientID___TaskID____Status
001________1________N
001________2________N
001________3________Y
002________1________Y
002________2________N
etc

You can use your task table to pre-populate your client/task table e.g. when a new client is added, then add 10 tasks to the client/task table (status=N)

If you get stuck or are unsure do ask or post your database of where you are up to.

 

[Runawaygeek]

Cheers buddy,

I think i am on the right path now!

B

 

[Runawaygeek]

Ok, So i think i have the tables linked and set up as required.. but now I am at a total loss on how to setup the form, so that it can populate these tables?

I think this is why i had the table as it was, more Spreadsheet, than data table.
I cant see how i get the "TASK_NAME" to show on the form and then the Input from that task to go to the other table??

I have not dont it this way before, any advice is welcome.


Would I build the Query first, then make the form from that? If i do, do I then need to link the "Source" for each field back to the VALUES table?

 

[sneuberg]

I suggest taking a break from your project and watch some YouTube flicks. You could start by watching this one and then google "ms access youtube subforms" and watch some more if you need to.

 

[Runawaygeek]

I cannot workout how to build a form that lists all the tasks :banghead:

I can get it to list task_Name, but as far as i can see forms only list Column headings, which is not how we have discussed the layout of the data? So I am totally lost now.

Either i have every TASK as its own column heading, but then I cant link any scoring to the TASK or I list the TASKS and cant actually build a form to have a value assigned?:banghead::banghead:

 

[sneuberg]

To create you form for the tasks which you will uses as a subform trying these steps:

1. Click on the tasks table in the navigation pane just to select it.
2. In the CREATE tab of the ribbon click on Form Wizard
3. Select the fields for the form (maybe you should start with all of them and delete later)
4. Select tabular for the layout of the form
5. Give the form a name and click finish

This should give you something to start with. Watch some more YouTube. What you need to know is there somewhere.

 

[Runawaygeek]

Hi Steve,

Thank you for that, its pretty good, but not really what i need.
The limiting factor on this, is you cant have multiple input types for the Values in the form. Such as Dropdowns, Yes/no, numbers or Text.

I need a Comprehensive form, that allows many input types, that then applies a value to a task, under a client. The reason for this structure is to allow a score against the task to allow for an insight in to complexity for each clients needs.
Allowing for more detailed budget allocations.

I am struggling to match this up

 

[sneuberg]

Could you describe your project with some sample input and output? How are these various inputs in Dropdowns, Yes/no, numbers or Text going to be evaluated to come up with a score. Forget computers for a moment and describe how you would do this with a pencil and paper.

 

[Runawaygeek]

Hi Steve,

I want to take a list of tasks and populate them for each client.
Each task is worth a weighting. (how long does it take for example) If i add up the Populated tasks for each client, i get the client score. However, some tasks are multiplied by the number in the box. EG "How many poster files are required?" submitting a poster file scores 10, so if there are 4 poster files, i need to = 40

My team use this record information to complete jobs for the clients.
this information can change, so they need to be able to update the task values for each client.
The uses cant be trusted to input the required information. This information is reported on, so even things like "Yes" need to be input right. (I have seen, yees, es, y, Y, 1, YES, yes, yyy, yer, yep as inputs for yes). Where possible i want to avoid free text, but in these cases i am simply doing ISNULL().

From a user perspective the form is vital as they use it as a visual reference. This data is currently held in a sheet, where each task is its own column heading and each row applies to a client.

I have another table, that lists the tasks and their Weights. The question is, how do i link all this??

 

[sneuberg]

I'm still confused. You could have all sorts of fields in the task table and if you have definite rules you can calculate a score. Let say you have some field with these rules

Field Name       Type             Rule
EG                 Number         ten times the number
GoodBad          Yes/No          5 if Yes 0 if No

The the calculated score field would be something like:

[EG] * 10 + IIF([GoodBad], 5,0)

If you can give me the rules, I can give you an expression.

 

[Runawaygeek]

actually the rules get even more complex, and involved some major SQL work on some of the other data in the database. But I have all that done.
Its the linking to the value thats the issue.
I dont want to write out a rule for each task, as if the complexity of the task changes due to development, then i need someone to be able to change the weighting applied to the task in a table.

So my rules would use the [TASK_WEIGHT] to keep them dynamic.

In its simplest terms, its like a questionnaire.

Table of Questions and their Score
Table of Customer Contact information
Table of answers.
Like any online questionnaire there are many data types

Here are my tables:

Task Table


Client Table (Client names hidden, but the Cols are ID and Name)


Potential Output Table


Here is the current task values, as you can see the tasks are headings in this table, which makes it impossible to apply weights. (Named Complexity weight) also there are more than in the image, but none i can share publicly.


This is the form that is currently populated by the table above, I need a form like this, that populates the 3rd table from the details in the 1st and second table..

dont worry about the score rules, I have that all planned, and it varies too much depending on the clients delivery type, work process type and some other things, so there is a large algorithm already built and working, it just needs the Weighting input from this issue.

 

[stopher]

The attached database shows how to add a list of tasks (from tblTasks) to a subform (based on tblClientTasks).

If you try to add tasks to a client that already has the tasks, nothing will happen. If you try to add tasks to a client with no tasks e.g. client 3, the tasks will be added.

I don't really follow what you are doing with the weightings etc but hopefully if you look at the table structure it will give you some ideas about how yours should be structured.

 

[sneuberg]

Here is the current task values, as you can see the tasks are headings in this table, which makes it impossible to apply weights. (Named Complexity weight) also there are more than in the image, but none i can share publicly.

If you restructured this into a single table with the column headings being field values I think I would be possible.

 

[Runawaygeek]

So you example is where i am at, on your tblClientTasks table,

Task one needs to be a number,
Task two needs to be Text
Task three needs to be YES/No

I have considered having task types (TEXT, NUMBER, YN) as column headings,
then working it out from that, but i think it will get messy.

It is currently a combined column with values as headings, but then, how would i relate the Weighting of a task to the name of a heading?

thanks for taking the time with this, i appreciate it!