Input data through a crosstab Query

[Banana]

gblack, actually, if you had read the thread, you would have seen that the OP was more or less answered, and the discussion had shifted to theoretical wantings.

BTW, since you've done that sort of stuff before, would you do me a favor and take a look at the classroom attendance prototype I linked to earlier and see how you like it and how it could be improved? I would love to hear from others about how it can be improved and hopefully translated into a useful model for other to adapt to their uses.

 

[gblack]

Lol

Yeah... I didn't actually see that there was an entire second page lol... oh well...

I'll take a look at the link.

 

[Jolrath]

Banana, new to this site, and can't see your link anywhere? I too am struggling to take off my spreadsheet hat on my first go at access.

All the experienced people here are clearly opting for not using the spreadsheet style entry, which makes me think I want to move away from it, but I was wondering what the alternative is when dealing with a reasonably large amount of data?

Our old excel sheet stored students across the columns, and progress statements as the rows. In a spreadsheet, 12 columns and 48 rows with dropdowns in each cell makes a nice little sheet to give to each teacher to fill in. Unfortunately in access this seems to want to be displayed as a table with 3 columns (child, statement, result) and 576 rows, and that's just for one class and one subject, one terms entry.

The final comment is that while I'm happy to learn access, many of the individual teachers will not be, so I need to either give them an excel sheet, or a really simple form to edit.

Could anyone tell me where I should be going for data entry once I leave spreadsheet/crosstab/pivot tables behind?

 

[Gasman]

You should have read post #5?
Doing so would have lead you here

HTH

 

[MajP]

@Jolrath
Not sure if you noticed but that thread is 12 years old so may not get a reply on it. My biggest gripe with Access is that is does not have an unbound grid control which would make doing things like this very easy. There is an active x Flexgrid, but it has a lot of issues and not sure if even supported anymore. In VB.net there is something called a gridview and it is awesome for doing this.
With that said you can fake this, with a little bit of code and using a temp table.

All the experienced people here are clearly opting for not using the spreadsheet style entry

That is not true. Doing entry spread sheet style is not an issue. It is storing data spread sheet style. The difference from Excel and Access is that in access the storage of data and display are two different things. In Excel your storage is your display.
If interested in replicating this data entry, I think I can demo something far easier than what that link shows. I have several similar dbs. I recommend staring a new thread. By easier I mean a lot less code and moving pieces. If I understand your columns would be students in a class. What are the 48 rows? Are those days? What are the values in the pulldowns?

 

[theDBguy]

Hi @Jolrath. Welcome to AWF

As already mentioned, what's important to know in Access is how you store the data. After that, you can display them any way you like for your users.

 

[CJ_London]

I believe the Flexgrid (or datagrid?) will still work in 32bit access, but is not supported in 64bit (and I have heard never will be) - so even if it works now, it will stop working at some point in the future if you or one of the users has 64bit access.

However you can create the equivalent of a grid control (for the purposes of data input) using an ADO disconnected recordset on a datasheet or continuous form.

as with a datagrid, you need code to populate the recordset and code to manage subsequent save/update events

 

[MajP]

Using an in memory ADO recordset or a temp table, the technique is almost identical. Read the noramalized data and enter it in a grid like pattern. On close read the grid like data and update the normalized tables. Both have their pros and cons.

 

[MajP]

The requirement is that the horizontal components are known in advance.

That is one of the advantages of using an in memory recordset. You do not have this limitation. You can dynamically build any amount of columns and rows. However, as stated there are other drawbacks.

 

[MajP]

The difference is my solution uses a bound form and only a couple of lines of code in the beforeUpdate event of each of the controls to populate the foreign key

Yes. I think I already addressed there are several option to do this and they each have pros and cons. So your solution is not the universal best solution. It depends on the requirement. I can think of three ways to meet the requirement, each has pros and cons.