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
> Insert Bulk Records Into Table, Access 2010    
 
   
kolos
post Mar 9 2020, 02:50 PM
Post#1



Posts: 30
Joined: 18-December 07



Hello,

I have a combo box filter on the form by which I filter the data in the query.
I present the filtered data in the same form in the subform.
The filtered data is from Table "A".
Query has the calculated values ​​of the data from Table "A"
This data should be entered in table "B".
Table "B" is a bit like the initial state of a warehouse.
What should I do. To make append query or something else ?
If my explanation is confusing, a sketch is attached.
I got stuck in step 4.

Attached File(s)
Attached File  prob.jpg ( 39.82K )Number of downloads: 9
 
Go to the top of the page
 
GroverParkGeorge
post Mar 9 2020, 03:01 PM
Post#2


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


"... the calculated values ​​of the data from Table "A"
This data should be entered in table "B". ..."

Actually, we generally do NOT store calculated values in a relational database application. We prefer to recalculate them from the stored component data as needed for REPORTS or other SUMMARY presentations.

Why do you want to do this?

--------------------
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
 
kolos
post Mar 9 2020, 03:17 PM
Post#3



Posts: 30
Joined: 18-December 07



Table B belongs to a separate section. Warehouse. I need to have a warehouse condition. Entrance and exit.
Go to the top of the page
 
kolos
post Mar 9 2020, 03:47 PM
Post#4



Posts: 30
Joined: 18-December 07



Can i use something like this ?

CODE
Dim SQL As String
SQL = _
    "INSERT INTO Table A (Field1, Field2, Field3...)  " & _
    "VALUES (""" & txbName & """, """ & txbName & """, """ & txbNumber & """, """ & txbNumber & ")"


Or i should use for subform
CODE
SubformControl.Form!ControlName


Am i need any delimiter for numeric fields ?

Go to the top of the page
 
GroverParkGeorge
post Mar 9 2020, 03:54 PM
Post#5


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


In theory, yes, but I'm still not clear on the "why" part. Are you MOVING units into a warehouse from another location? Are you taking delivery of new units directly into the warehouse? What is the workflow.

Re: delimiters in MS Access.

For strings, the delimiter is "
For dates, the delimiter is #
For numbers, there is no delimiter required.

Other relational database management systems may use different delimiters.

--------------------
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
 
kolos
post Mar 9 2020, 04:02 PM
Post#6



Posts: 30
Joined: 18-December 07



The unit to be stored in the warehouse can only be obtained by calculation.
Initially, there are values ​​of length and width of the wooden wall.
Based on these and some other values, should be calculated the number of elements as a part of those wall.
These values ​​are actually the condition of the warehouse after production.
Maybe another idea?
Go to the top of the page
 
MadPiet
post Mar 9 2020, 04:44 PM
Post#7



Posts: 3,792
Joined: 27-February 09



So are you storing something like point in time totals or something? What's the reason for storing the data like that?
Go to the top of the page
 
GroverParkGeorge
post Mar 9 2020, 04:47 PM
Post#8


UA Admin
Posts: 37,503
Joined: 20-June 02
From: Newcastle, WA


".... can only be obtained by calculation"

And that means, as I mentioned, your relational database application is probably best designed to do that calculation--or recalculation-- as needed for reports.

You might want to invest a little time into the study of this issue, why we prefer not to store calculated values and the rare, some might say exceedingly rare, instances where we violate that principal.

We store the individual data points that go into calculations because, once stored, they are constant in the table until, and unless, you need to correct or update one of those data points.

We typically do not store calculations because, should any of the original data points be altered, the calculation immediately goes out of validity, forcing a recalculation as well.


The exception to that rule would be something like a "price as of" calculation, where one of the components, say the sales price of a product can and will be changed over time. We do NOT want to go back and recalculate the "price as of" the original sale of that item at any point in the past. Those sorts of calculations are, therefore, sometimes stored. Otherwise, not.

Here, where you are building walls, I can't imagine that you ever need to know the volume of the materials in it as of five years ago compared to today. Therefore, storing the calculation is not required.

This is one of those primary principles of good relational database design that comes with the territory. Most developers figure it out sooner or later if they stick to the discipline very long.

--------------------
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
 
projecttoday
post Mar 9 2020, 05:07 PM
Post#9


UtterAccess VIP
Posts: 12,397
Joined: 10-February 04
From: South Charleston, WV


Are you trying to calculate the quantity in the warehouse?

--------------------
Robert Crouser
Go to the top of the page
 
kolos
post Mar 9 2020, 05:10 PM
Post#10



Posts: 30
Joined: 18-December 07



Yes, you are absolutely right.
Essentially, I can do anything with two tables by entering the entry date and pk and the release date and pk in the other table.
Calling the dates and pk via query I can filter the data and get the results I want at any time. Am I right ?
Go to the top of the page
 
projecttoday
post Mar 9 2020, 05:17 PM
Post#11


UtterAccess VIP
Posts: 12,397
Joined: 10-February 04
From: South Charleston, WV


Yes.

Quantity on hand.

--------------------
Robert Crouser
Go to the top of the page
 
kolos
post Mar 9 2020, 05:42 PM
Post#12



Posts: 30
Joined: 18-December 07



Thank you all for your help and good advice
Go to the top of the page
 
projecttoday
post Mar 9 2020, 06:54 PM
Post#13


UtterAccess VIP
Posts: 12,397
Joined: 10-February 04
From: South Charleston, WV


thumbup.gif

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 06:48 AM