Updateable Bound Forms with Denormalized Data with Pat Hartman (1 Viewer)

[GPGeorge]

Can you display and edit properly normalized data in a form which resembles a spreadsheet?


Although it sounds impossible, it can be done with the right technique. Pat Hartman shares her method with us.
She will show us the sample database containing two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is actually updateable. The technique uses a separate query for each column, then a master query to join all the “column” queries into a single recordset. It is limited but works well for 12 months. Using her downloadable sample databases as templates, you can expand the technique in your own applications.

Prepare for the presentation by downloading the files Pat will demonstrate and discuss.

Join us for a lively presentation and follow-up Q & A. We often range beyond the topic of the evening’s presentation, so plan on sharing your own ideas and experiences with your colleagues.

Please join us for our Monthly Chapter Presentation , at 6:30PM Pacific (UTC -8)

(Please verify your Time Zone offset with World Time Buddy as Daylight Savings changes are coming up soon.)
Access Pacific holds FREE Q&A webinars the First Thursday of every month, from 6:30 pm to 7:30 pm Pacific.
Add AUG Pacific meetings to your Outlook Calender

One of my favorite parts of most meetings has been when we end the recording and just talk. Usually we focus on the material in the presentation, but not always . We get a chance to stay connected with other working Access developers; that's invaluable and rare in this virtual world.
If you have ideas for a presentation, or a topic you'd like to explore, use this email or the feedback link on the AUG web site to let me know.
Access Pacific holds FREE Q&A webinars the First Thursday of every month, from 6:30 pm to 7:30 pm Pacific.
________________________________________
Please prepare ahead of time by downloading Zoom, if you don’t already have it.

Add the meeting to your Outlook calendar.

To join our meetings, please use the following link and phone number or use your computer’s speakers with audio:
When it’s time, join the meeting from here:
Join Zoom Meeting

 

[GPGeorge]

Please join former MS Access MVP Pat Hartman tonight at 6:30PM Pacific.

Her technique for editing denormalized data in a form which resembles a spreadsheet shows off the power and flexibility of Access as a data entry tool, regardless of the back end database used with it.

 

[NauticalGent]

Sorry I will miss this, Thursday night is bowling league...good thing we have YouTube!

 

[pbaldy]

Biker...bowler...it's all starting to make sense.

I'm hopeful to make the meeting, though it occurs at dinner time here. I have a case where it would be very helpful. I've played with the sample but can't get mine to be editable.

 

[Minty]

I have had to implement a "Excel like" solution for a client, and the only solution with the desired flexibility (variable number of columns, variable number of headings) was a temporary local table, and a lock on anyone else editing those records.

It works really well, and is fast, even the writing back of the data to the BE tables, is really quick, but it took a considerable amount of fiddling to get it right.

I'll be interested in looking at Pat's presentation, to see if I'm missing a trick.

 

[adhoustonj]

I wish I could have attended this! Hopefully there will be a recording posted so that I can catch up.

 

[MajP]

Here is a current example demonstrating some of these techniques.
Thread 'Schedule grid' https://www.access-programmers.co.uk/forums/threads/schedule-grid.328898/

 

[GPGeorge]

I'll be editing the video for upload over the weekend. Early next week is the target. However, if my granddaughter decides she needs me to read to her, you're on your own for a few days longer.

 

[NauticalGent]

However, if my granddaughter decides she needs me to read to her, you're on your own for a few days longer.

Top-tier priority!

 

[GPGeorge]

Top-tier priority!

We actually ended up doing a different activity. My 1 year old granddaughter taught me a new game yesterday. I think she invented it herself.
It's called "Uh Oh". Here's how you play.

Emma picks up an object -- one of her socks, a lid from a reusable water bottle, a sippy cup lid, a plastic container, one of Daddy's books or even Grandpa's iPhone (once). They all work.

Then Emma drops or throws the object while saying "Uh Oh". Then it's Grandpa's turn to play. He picks the object up while Emma laughs until drool comes out of her mouth.

We had a great time playing "Uh Oh" for at least 20 minutes and one of us enjoyed it for another 20. Then Grandpa took a nap on the couch.

Her mom and dad are less enthusiastic about "Uh Oh" I'm afraid, but like I say, it was Emma's idea, not mine.

 

[pbaldy]

LOL! That sounds like a game my grandkids would come up with.

 

[Mike Krailo]

You're so lucky GPGeorge, having grand kids. I don't have that luxury, so I'm missing out. I have to substitute our German short hair pointer for a kid and her uh oh moment is when we are driving her to the vet for a checkup or nail trim. The look on her face is unmistakable. Uh oh....

 

[Pat Hartman]

Grandchildren are the best. You can love them and spoil them and play "uh oh" until you want to run screaming out of the room. So, instead, you send them home to mommy. I had the good fortune to have my daughter and the twins living with me for the first 4 years of their lives.

 

[isladogs]

or even Grandpa's iPhone (once). They all work.

Hopefully Grandpa's iPhone still works!

As well as the joy of grandchildren, I've had the pleasure today of trying to work whilst the new puppy (Ruby) crawled all over me and jumped up & down on the keyboard. Lots of uh-oh moments and not a lot achieved.

Anyway, I'm just about to upload the video of last week's Access Europe session led by Juan Soto. It should be live in the morning (UK time)

 

[Pat Hartman]

My cat used to want to lay down on my keyboard. He would also lay on the pile of legos on the floor and jump up on the table where we were playing cards.

 

[isladogs]

Taking us even further off topic (triggered by George originally), my elderly cat almost certainly has dementia and has recently taken to sleeping in his litter tray after using it and us cleaning it out. Yuck!

 

[GPGeorge]

Trying to get back on track, there's a comment on the now-published video regarding upsert queries and SQL Server. Do you want to take a look and maybe respond?

 

[cheekybuddha]

Trying to get back on track, there's a comment on the now-published video regarding upsert queries and SQL Server. Do you want to take a look and maybe respond?

There's something really wrong with that video, @GPGeorge.

I suspect it's to so with the what the commenter who was complaining about adverts was saying.

I see a sort of modal (web style) appearing over the main video with the 'window' changing position every few seconds.

Finally the 'window' filled with an ad for Azure services, then one for google.

Will try and record my screen so you can see what I mean.

 

[cheekybuddha]

Link to the video on my pCloud here (19.7 MB - too large to attach)

Start from the 27 or 28 second mark, I captured a bit of Pat giving us her bio by accident to begin with.

I scrobble along in a couple of places to see more quickly too.

I have an ad-blocker, which is probably why the 'window' is empty some of the time.

 

[GPGeorge]

Thanks, I had spot checked and saw no problems, but streaming it I can see many issues. I'll have to reedit the whole $%jk@1 thing.