Input data through a crosstab Query

[Lost in db land]

Access 2003 - Windows Vista - Novice programmer (so be nice)

I have 3 tables:

documents
Issues
Revisions

I need all 3 values to be user expandable.

I have a central table that has lookup fields for all 3 values - I can then do a crosstab query that shows them in a nice matrix with documents as the row header, Issues as the column headers and revision as the value in the middle - great.

I want to streamline the input of the data - so that the documents and issues in the crosstab auto update and I can then just enter the revision letter in the middle of the crosstab - only you can't enter the data in the crosstab - please help me!

 

[boblarson]

You can't enter data as a crosstab. So you would need to use unbound text boxes and load things that way. Not a fun prospect. I had to do that recently for a current project I'm working on. It wasn't too hard iterating through a recordset to pull the data. It was harder when trying to update the data.

 

[Lost in db land]

wow! thanks for the quick reply -

now that you've confirmed that i can't enter the data through a crosstabQ -

I've managed a bit of a workaround and could do with some help refining it

If I can populate a table with the results of a select query that produces a record for every combination of document and issue - and has a field for revision - I can then use filters on a form to make the data entry a bit more friendly so lets ignore the crosstab layout/data entry bit for now.

so next question - how do I populate a table with the results of a query (that i can add ducuments and issues to as the job progressses)?

a maketable query gives me all the records i need - but i need to add the lookup field for revision to it and then continuously update the result as more documents and issues arise - and every time i run the maketable it overwrites the previously entered revisions for previous issues!

Thanks again.
Mark

 

[boblarson]

Have you ever looked into using an append query? Also, something is not sounding right based on your response. Are you talking about adding fields? And when you say "lookup field" what are you talking about?

 

[Banana]

FWIW, take a look at my prototype for class attendance. If you search the board "class attendance prototype" you should find a sample I uploaded that shows how to do a data input with spreadsheet-like form.

 

[Pat Hartman]

Dear Lost,
If you take off your spreadsheet hat, you'll find Access is really quite easy to work with. Generally relational tables are "sparse". That means that only rows that contain data will exist. So your idea of creating a Cartesian Product to produce every combination of key values is not standard practice. You can use tables and queries interchangeably so you can base your form on a query (which is better practice anyway). Use combo boxes to allow the user to select values from a list. This way you will only enter rows that have values.

 

[Lost in db land]

The reason i want a spreadsheet look is that I want to eventually add this db to an existing db - this bit has always been done as a spreadsheet traditionally - and there were always incosistencies between the project DB and issue spreadsheet - obviously just because the data has been traditonally entered that way doesn't mean it has to carry on that way but i think it would be a nice touch.

Bob - I looked at an append query - but it seemed to add all the results of the query regardless of whether they were new or not - this could be me not specifying 'append new docs only' - is this possible?

When I said lookup field I meant a combo-box to select a value from another table (with an one to many enforced relationship)

Banana - That's the look i'm aiming for - but with a combo box with a value in it instead of a check box.

Pat - if I base the form on a query i cannot use it to enter data - or can I??

The work around I was trying to explain needs table based on three fields - all from other tables - The first two fields auto-fill with every combination of record from the first two tables so I can enter the revision I imagine it would look/work like this:

Doc Issue Revision

1____1____ A
1___ 2_____ B
2___ 1_____ A
2___ 2 _____B

Then if I add a third doc to the list the next time i open this table i want it to look like this:

Doc Issue Rev
1____1 ___A
1 ___2____B
2____1____A
2____2____B
3____1
3____2

Or when i add a third issue to the list:

1____1____A
1____2____B
1____3____

etc...so I can enter add Rev letters to the new records.

If I filter the records on a form by issue I can end up with:

_______Issue 1_____Issue2_____Issue 3
Day:_____01
Month:___04
Year:____08
Reason:__I_ Etc=>


Doc______Rev__________Rev_______Rev
1_________A__________B_______(Select data Here)
2_________A__________B_______(select data Here)
3_________X__________X_______(select data Here)

Which looks a lot like a crosstab query but i want to be able to edit the Rev letters.

This is a very complex way of doing something simple(to me) which is why I came to you folks before I try and learn SQL/VB to make it all happen.

I know i can just enter 100 Issues into the table to start with - but then we'll issue a doc 101 times!

Again - Thanks for all your responses - I hope i'm being clear enough.

 

[Banana]

Lost in db,

Reading your scenario, it doesn't strike me as apropos for using a spreadsheet-like entry. I would just have a form that uses the table of document as a recordsource, then have a subform for the revision. This way, you see one document per, but you can view several revisions within the subform.

This would be far more easier and would require no coding, whereas maintaining spreadsheet look would be *far* more complex. The only reason I would bother with it is if I had one record that has two interdependent children (in case of class attendance, that's session and attendance to a class record) and it was easier to be able to see it in a grid. This is a minority in database world, for certain.

 

[Lost in db land]

Thanks everyone - I'm going to carry on without the spreadsheet look for now.

I would like to acheive this method of data entry though - and i don't think it should be impossible so if anyone feels like pointing me in the right direction instead of talking me out of it then pls post.

 

[Pat Hartman]

The reasons we are trying to talk you out of it:
1. The data should not be stored as a grid. It should be normalized.
2. You will need to use unbound forms to work with normalized data as a grid.
3. The programming effort involved is significant and if you don't already know VBA, you'll be in way over your head.
4. For display purposes, you can use a crosstab query so you can present the data as a grid even though it is not updated as a grid.

 

[Alisa]

Lost, I got the same advice as you did a while back when I was struggling with the same thing. My solution is to store the data in the correct, i.e., normalized format. When the user wants to edit the data, I write the results of a crosstab query on the data to a temp table, and display the form which is bound to the temp table. Then when they close the form, I renormalize the data and write it back to the original table. This is relatively unsatisfactory because it is very slow for a large amount of data, and I have to make sure that 2 people don't go in at the same time.

I think it is a shame that access doesn't provide a way to do an editable crosstab. There are valid circumstances when editing in a crosstab really is the best way to do it, i.e., when you are not displaying aggregate results, but you are really dealing with unique values and you really have a value for every slot in the matrix. It seems like the folks over at microsoft could implement this if they really wanted to.

I continue to struggle with this, you can see I just started another thread about it today. Anyway, good luck, and I just wanted to let you know that it is possible, although painful.

 

[Lost in db land]

Pat - Oops! maybe i sounded a bit ungrateful! I feel that programs should do what you want them to rather than make you do things a specific way - I was just trying to get over the 'you shouldn't do that because...' and try and get to the 'well if you do insist on doing it that way then try this...'

Alisa - It's good to know I'm not the only person who needs to enter data through a crosstab/matrix - I seem to be entering data through a never-ending tree of forms, filters, Queries and Tables just to acheive a simple expandable editable matrix.

I'm not dealing with huge amounts of data - I may give it a go as a theoretical/training exercise one day.

Thanks all

 

[Banana]

Here's a quick'n'dirty hack.

Create a crosstab query as the usual. Use a form to display the query. Of course, that will be nonupdateable, but on every controls used for displaying the values, add a OnClick event to open a form where you can edit the values (or maybe move it to a different column if desired). Once done, user closes the form, then the first form requeries the crosstab query and updates the results.

That would be easier and simpler than making a truly updateable crosstab query which you can see from my sample is not a easy task, and that's just for a simple binary value.

 

[Pat Hartman]

A crosstab is an aggregation of data. When you aggregate data, there is no way to keep the identity of individual records. Without being able to identify individual records, there is no way to update them. Now, as it happens, there are some cases where you are using a crosstab that isn't really aggregating, it is just pivoting. It would be nice to be able to update in this case but this situation would be almost impossible to detect and so updating is not feasible. The suggestion to use a make-table and then renormalize is probably the method that will require the least coding.

 

[Alisa]

A crosstab is an aggregation of data. When you aggregate data, there is no way to keep the identity of individual records. Without being able to identify individual records, there is no way to update them. Now, as it happens, there are some cases where you are using a crosstab that isn't really aggregating, it is just pivoting. It would be nice to be able to update in this case but this situation would be almost impossible to detect and so updating is not feasible. The suggestion to use a make-table and then renormalize is probably the method that will require the least coding.

Good distinction between pivoting and aggregating. I don't see why individual records couldn't be identified though - they are clearly identified by their "row" and their "column". Also, it is very simple to detect when the user is pivoting instead of aggregating - when you pivot, you use FirstOf value, when you aggregate you use a function like sum, min, max, etc.

 

[Banana]

Alisa,

I perceive this as problem of identifying which record; yes we know what jurisdiction we are at, but suppose we had a 2+2=4, and we wanted the value to be 5, not 4, but how does Access know which to put in extra one, should it be 3+2=5 or 2+3=5? Database theory is based on the idea that we should only handle *one* record at a time, never with a collection of records, and it's not a shortcoming of Access, this is true of just about any database engine I know of.

Even if you were merely pivoting, I imagine it would be difficult to guarantee that there are truly clearly traceable lineage to the source where you want to alter the value in such case, even if it's clear to our human brain, it's not so with computers. (They're dumber than a bucket of nuts, you know?)

 

[Alisa]

Alisa,

I perceive this as problem of identifying which record; yes we know what jurisdiction we are at, but suppose we had a 2+2=4, and we wanted the value to be 5, not 4, but how does Access know which to put in extra one, should it be 3+2=5 or 2+3=5? Database theory is based on the idea that we should only handle *one* record at a time, never with a collection of records, and it's not a shortcoming of Access, this is true of just about any database engine I know of.

I completely agree that with aggregated data, it makes no sense at all.

Even if you were merely pivoting, I imagine it would be difficult to guarantee that there are truly clearly traceable lineage to the source where you want to alter the value in such case, even if it's clear to our human brain, it's not so with computers. (They're dumber than a bucket of nuts, you know?)

In my particular case, there is absolutely one and only one value for every "cell" in the grid. Every time a new X or Y is entered, all of the records that would correspond to open cells on the grid are automatically entered as well. It is as if you were doing a multiplication table. Say you wanted to store the data of a multiplication table. The data would be like this

N.1/N.2/product
1 1 1
2 1 2
1 2 2
2 2 4


If you entered 3 as a new category, then you would automatically enter
1 3 3
2 3 6
3 3 9
3 1 3
3 2 6

Now imagine how much easier it would be to enter the products in the grid, as we are used to seeing a multiplication table, rather than some other format that Access prefers. My case is ofcourse a little bit more complicated than this - instead of numbers, I am dealing with primary keys, and instead of products, I have text values that have to be entered. In any case, my point is just that there is an absolutely legitimate need to have an editable pivot. In this case, each value in the grid really does represent one record. And yet I can't edit it.

 

[Banana]

Alisa,

I quite understand- Maybe I should make it a bit clearer: it's a shortcoming of the relational database theory. To model this accurately, we would have to move to something which the name escapes my memory- I think it's entity model instead of relational model, but will need to look it up.

This is why I posted that sample database earlier, because users wanted a grid of all sessions and tell at a glance the attendance while adding the today's roll call. Works quite well, and I don't imagine it's not impossible to do with a value data instead of checkbox; just will require a lot more work on your part.

 

[georgedwilkinson]

Object Oriented databases aren't in common use because they don't work right. Relational model rules.

 

[Banana]

Yep, and not just right but can get very complicated, whereas a similar relational model (after adjusting for its shortcomings) can be had in matter of minutes.