INSERT INTO from both a table and memory variable

  • Thread starter Thread starter Todd F.
  • Start date Start date
T

Todd F.

I want to insert a new record into Table A which consists of 4 values. Three
of those values are to be retrieved from Table B. The fourth is the value
entered by the user into a memory variable. Is this possible? If so, what
is the correct syntax? If it's not possible, what's an alternative
workaround? Thanks.
 
Todd

To be able to offer a "workaround", we'd need to know what problem you are
trying to solve by copying data from one table to another.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Without getting too technical, here's the situation.

We have a page tracking database in Access. Each time a new page arrives
in-house, we enter the relevant information.

Pages fall into 3 main categories:

1) Standalone pages which are completed just once for a given subject
(e.g., demographics).

2) Standalone pages which can be completed multiple times for a given
subject (e.g., medications). Their page numbers consist of a root page
number and an extension (e.g., 50.01, 50.02, etc.).

3) Grouped pages which can be completed multiple times for a given subject.
They are grouped by timepoint (e.g., 3 months, 6 months, etc.) and have page
numbers with extensions.

Currently, when category 2) pages are tracked, the user enters values into
memory variables, which in turn are inserted into the new record.

Conversely, when category 3) pages are tracked, values are retrieved from a
master table and inserted into the new record.

We have now encountered a situation where the page number extension also
needs to be inserted into the new record for category 3) pages. This would
still come from user input, while the other 3 values would still be retrieved
from the master table.

Hope that clears things up a bit!
 
I want to insert a new record into Table A which consists of 4 values. Three
of those values are to be retrieved from Table B. The fourth is the value
entered by the user into a memory variable. Is this possible? If so, what
is the correct syntax? If it's not possible, what's an alternative
workaround? Thanks.
Click to expand...

Depends on the context, but the simplest way would probably be to construct
the SQL string in VBA code; e.g.

Dim strSQL As String
Dim strWhy As String
strWhy = InputBox("Why, oh why?")
strSQL = "INSERT INTO B([who], [what], [when], [why]) " _
& " SELECT [who], [what], [when], '" & strWhy & "' FROM A;"
 
Back
Top