Input data through a crosstab Query (1 Viewer)

Alisa

Registered User.
Local time
Today, 03:11
Joined
Jun 8, 2007
Messages
1,931
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

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2002
Messages
42,981
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

Registered User.
Local time
Today, 03:11
Joined
Jun 8, 2007
Messages
1,931
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

Registered User.
Local time
Today, 09:11
Joined
Sep 18, 2002
Messages
632
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

split with a cherry atop.
Local time
Today, 02:11
Joined
Sep 1, 2005
Messages
6,318
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

Registered User.
Local time
Today, 09:11
Joined
Sep 18, 2002
Messages
632
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

New member
Local time
Today, 09:11
Joined
Sep 20, 2020
Messages
1
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

Enthusiastic Amateur
Local time
Today, 09:11
Joined
Sep 21, 2011
Messages
14,050
You should have read post #5?
Doing so would have lead you here

HTH
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:11
Joined
May 21, 2018
Messages
8,463
@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

I’m here to help
Staff member
Local time
Today, 02:11
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 09:11
Joined
Feb 19, 2013
Messages
16,553
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

You've got your good things, and you've got mine.
Local time
Today, 05:11
Joined
May 21, 2018
Messages
8,463
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

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2002
Messages
42,981
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.
 

Attachments

  • BoundDenormalizedForm20190212.zip
    1.5 MB · Views: 77

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:11
Joined
May 21, 2018
Messages
8,463
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

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2002
Messages
42,981
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

You've got your good things, and you've got mine.
Local time
Today, 05:11
Joined
May 21, 2018
Messages
8,463
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.
 

Users who are viewing this thread

Top Bottom