UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Can I Store Query Results In Variables?, Access 2016    
 
   
marcuscatan
post Jan 22 2020, 10:07 AM
Post#1



Posts: 15
Joined: 20-January 20



Hello all,

My situation is: I work at a plant and I'm building the access to gather all info about bulk production. Basically whenever someone finishes a production they have to open the file and input the info about what they just did. But it's really A LOT of info, my table got to 180 columns and I had to split in 3 to put all validation rules etc otherwise it would return me the error "Property value is too large".
I use a navigation form to open all of subsequent forms and the first one is Data entry whilst the others have a code to input info in the same record created by the first one.
Also, when I open the first form, it creates lines with same referential number on the other 2 tables, so that I can have a reference number on all 3.

The tables are:
Database_Gerencial
Database_B
Database_C

This is the last challenge I'm facing to deliver this project, what I need is: when someone starts a record they will input which platform the bulk was produced in [SkidBox] (our equipments are called Skids) and I need to get the info about the last record for that specific Skid.

The process has a lot of steps and some of these steps are not required depending on the production recipe the person is following. Which means that I can't just check a column and be sure that is the last record of that skid. I have to check the most recent date on all steps and get the maximum of that date.

I need these in variables so that I can put durations in my forms (the guy will input he is starting now, I need to get the time from the last bulk produced on this skid, subtract from now and give the time difference on the form).

I don't know if there is an easy way to do this, but I'm cracking my head on it for days. And as a plus, I'm afraid this consults might fail because I intend on spliting the database in front and back end and I don't know if this will be able to consult the back end...

I know this is probably a challenge. Thanks in advance for everyone who tries! notworthy.gif
Go to the top of the page
 
theDBguy
post Jan 22 2020, 10:48 AM
Post#2


UA Moderator
Posts: 77,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

Have you shown your table structure to anyone? I am just curious if your table structure is properly normalized. Splitting a table into three with the same primary key sounds like a bad design.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
marcuscatan
post Jan 22 2020, 11:20 AM
Post#3



Posts: 15
Joined: 20-January 20



Thanks!!

I did show and I kind of know it's a bad design I just didn't have the time nor the expertise to invest in a better architecture for it, and as I already have everything working (besides this last problem) I figured I'd go on with it anyways... The due date was last week, I really have to finish it ASAP frown.gif
Go to the top of the page
 
theDBguy
post Jan 22 2020, 11:24 AM
Post#4


UA Moderator
Posts: 77,505
Joined: 19-June 07
From: SunnySandyEggo


Okay, so if you're asking how to keep the result of a SELECT query in a variable, then the answer is to use a Recordset object. Hope that helps...

PS. I hope you realize by continuing to work with your current structure design, you are bound to hit a lot of walls along the way. Good luck.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
orange999
post Jan 22 2020, 11:37 AM
Post#5



Posts: 2,049
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Most of us have been in the situation where "we're too busy chopping wood to stop and sharpen the axe".
But, as the DBguy has suggested, I agree that your structure will come back to haunt you. If this database is important to your organization, then phase 2 might be to restructure using database concepts.

--------------------
Good luck with your project!
Go to the top of the page
 
projecttoday
post Jan 22 2020, 11:39 AM
Post#6


UtterAccess VIP
Posts: 11,594
Joined: 10-February 04
From: South Charleston, WV


If you want to display today's date minus the most-recent or something of that sort on a form you can use Dmax, Dlookup, etc. which has a where clause so you can select based on the current account.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Jan 22 2020, 12:10 PM
Post#7


UA Admin
Posts: 36,770
Joined: 20-June 02
From: Newcastle, WA


I know I'm piling on, but the time to put in a solid foundation under your house is BEFORE you start erecting walls and nailing on the roof....

The time to implement a valid table design is BEFORE you start writing unwieldy code, SQL, forms, reports, et al.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
marcuscatan
post Jan 23 2020, 02:38 PM
Post#8



Posts: 15
Joined: 20-January 20



Thanks for the answer
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 05:56 PM