Input data through a crosstab Query

[Alisa]

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.

I know, but despite the fact that the relational model isn't set up to do this sort of thing, I think this is one case where Microsoft could have built in some sort of workaround, the same way they built the crosstab query wizard, so I just take any opportunity to voice my opinion that you SHOULD be able to have an editable crosstab. At the very least they could have included an UNcrosstab wizard as well.

 

[Pat Hartman]

Alisa,
Although "First/Last" don't aggregate, they do choose from potentially many and that is the issue. Just because you "know" that the pivot is pure doesn't mean the the database engine should also.

BTW, you can do a crosstab with any varient of SQL. The syntax is slightly different. Each relational database creator (Microsoft (SQL Server & Jet), IBM (DB2), Oracle, Sybase, MySQL, etc., implements a basic core set of functionality. And some add additional features.

 

[Alisa]

Alisa,
Although "First/Last" don't aggregate, they do choose from potentially many and that is the issue. Just because you "know" that the pivot is pure doesn't mean the the database engine should also.

Yes that's true. However, it is possible to verify whether there is or is not aggregation in any potential crosstab. If I can do it, Access should be able to do it too. The only point I am trying to make here is that MS COULD make an editable crosstab wizard if they wanted to. It is possible, and it would add enormous value to the product.

 

[gblack]

Cartesian Product

Well... if you really want to use a Cartesian product (i.e. all possible choices from all the tables)... That's pretty easily done.


Just make a select query with the tables in question but don't link the tables together, go to the SQL tab and click it. When you see the SQL code... just after "SELECT" put in the word "DISTINCT" (This will ensure you get only one record for each iteration), and then run the query.

--------------------------------------------------

Then, if that result is really what you are looking for, you can turn that query into a static table (which might be easier to use than a query with no links) by taking your select query and turning it into a “make table query” and running it... You’ll have to update (i.e. rerun) that query every time the data changes, but you can get your code to do that.

Anyway…

I've done things like this all the time with small amounts of data where I needed to link up the fields and show zero or null categories for crosstabs, or when I need all the 15 minute intervals for every day of the month laid out…

-------------------------------------------

Honestly, I didn’t read the thread thoroughly enough to figure out exactly what you are trying to do… but its sounds like that's what you want.

Normalized data is nice and all... but sometimes getting it done now is more important that appeasing the database administrator’s sensibilities lol


HTH
Gary

 

[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.

 

[Pat Hartman]

Sometime during the past 12 years, I developed a form that looks like a grid but is based on normalized tables and lots of queries. The requirement is that the horizontal components are known in advance. So, it is most useful for calendar situations where you are showing, 7 days, 12 months, or perhaps 31 days across but I haven't tried one with that many queries.

 

[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.

 

[Pat Hartman]

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.

 

[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.