Make table from query

  • Thread starter Thread starter THINKINGWAY
  • Start date Start date
T

THINKINGWAY

I created a query that makes a table. I want one field in each record of the
new table to be a date input by the user (this date is entered once). I have
successfully created the make table. See sql view below. I even get the
prompt. But instead of the date I enter at the prompt, I get the date of the
record from the table (tblPROJFHIST). The idea is to create a unique
transaction in tblPROJFHIST because one or more of the dates has changed for
a particular project as read from tblWMSNEW (which is imported from a
spreadsheet).

SELECT tblWMSNEW.PROJECTS_NO, FHIST_RPT_WEEK, tblWMSNEW.PROJFH_FS_SS11XX,
tblWMSNEW.PROJFH_FS_CD1230, tblWMSNEW.PROJFH_FS_PER1310,
tblWMSNEW.PROJFH_FC_SS11XX, tblWMSNEW.PROJFH_FC_CD1230,
tblWMSNEW.PROJFH_FC_PER1310, tblWMSNEW.PROJFH_AS_SS11XX,
tblWMSNEW.PROJFH_AS_CD1230, tblWMSNEW.PROJFH_AS_PER1310,
tblWMSNEW.PROJFH_AC_SS11XX, tblWMSNEW.PROJFH_AC_CD1230,
tblWMSNEW.PROJFH_AC_PER1310 INTO tblTEMP1
FROM tblWMSNEW LEFT JOIN tblPROJFHIST ON tblWMSNEW.PROJECTS_NO =
tblPROJFHIST.PROJECTS_NO
WHERE (((FHIST_RPT_WEEK)=[Enter fiscal history report week:]) AND
((tblPROJFHIST.PROJFH_FS_SS011XX)<>[tblWMSNEW]![PROJFH_FS_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AC_SS011XX)<>[tblWMSNEW]![PROJFH_AC_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AC_CD01230)<>[tblWMSNEW]![PROJFH_AC_CD1230])) OR
(((tblPROJFHIST.PROJFH_AC_PER01310)<>[tblWMSNEW]![PROJFH_AC_PER1310])) OR
(((tblPROJFHIST.PROJFH_FS_CD01230)<>[tblWMSNEW]![PROJFH_FS_CD1230])) OR
(((tblPROJFHIST.PROJFH_FS_PER01310)<>[tblWMSNEW]![PROJFH_FS_PER1310])) OR
(((tblPROJFHIST.PROJFH_FC_SS011XX)<>[tblWMSNEW]![PROJFH_FC_SS11XX])) OR
(((tblPROJFHIST.PROJFH_FC_CD01230)<>[tblWMSNEW]![PROJFH_FC_CD1230])) OR
(((tblPROJFHIST.PROJFH_FC_PER01310)<>[tblWMSNEW]![PROJFH_FC_PER1310])) OR
(((tblPROJFHIST.PROJFH_AS_SS011XX)<>[tblWMSNEW]![PROJFH_AS_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AS_CD01230)<>[tblWMSNEW]![PROJFH_AS_CD1230])) OR
(((tblPROJFHIST.PROJFH_AS_PER01310)<>[tblWMSNEW]![PROJFH_AS_PER1310]));
 
In a lot of instances, a query that returns values that can be used
elsewhere is mistakenly converted to a make-table query.

I don't understand enough about your situation to tell if your "made" table
holds redundant data, or may be a new, relationally-oriented dataset. One
clue I'd use is whether the made table is being continually re-made, with
new data each time.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The made table is made with new data each time. Think of the new table as a
table containing transactions that hold (instead of monetary) dates. But I
need to enter just one date to denote the fiscal history week beginning time.
The other dates are all imported. The FC*, FS*, AC* and AS* are all
imported dates from the spread sheet. The FHIST_RPT_WEEK is the date I want
to the user to input when they run the make table query.

Jeff Boyce said:
In a lot of instances, a query that returns values that can be used
elsewhere is mistakenly converted to a make-table query.

I don't understand enough about your situation to tell if your "made" table
holds redundant data, or may be a new, relationally-oriented dataset. One
clue I'd use is whether the made table is being continually re-made, with
new data each time.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

THINKINGWAY said:
I created a query that makes a table. I want one field in each record of
the
new table to be a date input by the user (this date is entered once). I
have
successfully created the make table. See sql view below. I even get the
prompt. But instead of the date I enter at the prompt, I get the date of
the
record from the table (tblPROJFHIST). The idea is to create a unique
transaction in tblPROJFHIST because one or more of the dates has changed
for
a particular project as read from tblWMSNEW (which is imported from a
spreadsheet).

SELECT tblWMSNEW.PROJECTS_NO, FHIST_RPT_WEEK, tblWMSNEW.PROJFH_FS_SS11XX,
tblWMSNEW.PROJFH_FS_CD1230, tblWMSNEW.PROJFH_FS_PER1310,
tblWMSNEW.PROJFH_FC_SS11XX, tblWMSNEW.PROJFH_FC_CD1230,
tblWMSNEW.PROJFH_FC_PER1310, tblWMSNEW.PROJFH_AS_SS11XX,
tblWMSNEW.PROJFH_AS_CD1230, tblWMSNEW.PROJFH_AS_PER1310,
tblWMSNEW.PROJFH_AC_SS11XX, tblWMSNEW.PROJFH_AC_CD1230,
tblWMSNEW.PROJFH_AC_PER1310 INTO tblTEMP1
FROM tblWMSNEW LEFT JOIN tblPROJFHIST ON tblWMSNEW.PROJECTS_NO =
tblPROJFHIST.PROJECTS_NO
WHERE (((FHIST_RPT_WEEK)=[Enter fiscal history report week:]) AND
((tblPROJFHIST.PROJFH_FS_SS011XX)<>[tblWMSNEW]![PROJFH_FS_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AC_SS011XX)<>[tblWMSNEW]![PROJFH_AC_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AC_CD01230)<>[tblWMSNEW]![PROJFH_AC_CD1230])) OR
(((tblPROJFHIST.PROJFH_AC_PER01310)<>[tblWMSNEW]![PROJFH_AC_PER1310])) OR
(((tblPROJFHIST.PROJFH_FS_CD01230)<>[tblWMSNEW]![PROJFH_FS_CD1230])) OR
(((tblPROJFHIST.PROJFH_FS_PER01310)<>[tblWMSNEW]![PROJFH_FS_PER1310])) OR
(((tblPROJFHIST.PROJFH_FC_SS011XX)<>[tblWMSNEW]![PROJFH_FC_SS11XX])) OR
(((tblPROJFHIST.PROJFH_FC_CD01230)<>[tblWMSNEW]![PROJFH_FC_CD1230])) OR
(((tblPROJFHIST.PROJFH_FC_PER01310)<>[tblWMSNEW]![PROJFH_FC_PER1310])) OR
(((tblPROJFHIST.PROJFH_AS_SS011XX)<>[tblWMSNEW]![PROJFH_AS_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AS_CD01230)<>[tblWMSNEW]![PROJFH_AS_CD1230])) OR
(((tblPROJFHIST.PROJFH_AS_PER01310)<>[tblWMSNEW]![PROJFH_AS_PER1310]));
Click to expand...
Click to expand...
 
It all starts with the data.

I don't have any idea what data you're starting with.

I (still) don't understand why you need a "made" table -- what are you doing
with what ends up in that table that you couldn't do if you used a simple
select query instead?

Regards

Jeff Boyce
Microsoft Office/Access MVP

THINKINGWAY said:
The made table is made with new data each time. Think of the new table
as a
table containing transactions that hold (instead of monetary) dates. But
I
need to enter just one date to denote the fiscal history week beginning
time.
The other dates are all imported. The FC*, FS*, AC* and AS* are all
imported dates from the spread sheet. The FHIST_RPT_WEEK is the date I
want
to the user to input when they run the make table query.

Jeff Boyce said:
In a lot of instances, a query that returns values that can be used
elsewhere is mistakenly converted to a make-table query.

I don't understand enough about your situation to tell if your "made"
table
holds redundant data, or may be a new, relationally-oriented dataset.
One
clue I'd use is whether the made table is being continually re-made, with
new data each time.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

THINKINGWAY said:
I created a query that makes a table. I want one field in each record
of
the
new table to be a date input by the user (this date is entered once).
I
have
successfully created the make table. See sql view below. I even get
the
prompt. But instead of the date I enter at the prompt, I get the date
of
the
record from the table (tblPROJFHIST). The idea is to create a unique
transaction in tblPROJFHIST because one or more of the dates has
changed
for
a particular project as read from tblWMSNEW (which is imported from a
spreadsheet).

SELECT tblWMSNEW.PROJECTS_NO, FHIST_RPT_WEEK,
tblWMSNEW.PROJFH_FS_SS11XX,
tblWMSNEW.PROJFH_FS_CD1230, tblWMSNEW.PROJFH_FS_PER1310,
tblWMSNEW.PROJFH_FC_SS11XX, tblWMSNEW.PROJFH_FC_CD1230,
tblWMSNEW.PROJFH_FC_PER1310, tblWMSNEW.PROJFH_AS_SS11XX,
tblWMSNEW.PROJFH_AS_CD1230, tblWMSNEW.PROJFH_AS_PER1310,
tblWMSNEW.PROJFH_AC_SS11XX, tblWMSNEW.PROJFH_AC_CD1230,
tblWMSNEW.PROJFH_AC_PER1310 INTO tblTEMP1
FROM tblWMSNEW LEFT JOIN tblPROJFHIST ON tblWMSNEW.PROJECTS_NO =
tblPROJFHIST.PROJECTS_NO
WHERE (((FHIST_RPT_WEEK)=[Enter fiscal history report week:]) AND
((tblPROJFHIST.PROJFH_FS_SS011XX)<>[tblWMSNEW]![PROJFH_FS_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AC_SS011XX)<>[tblWMSNEW]![PROJFH_AC_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AC_CD01230)<>[tblWMSNEW]![PROJFH_AC_CD1230])) OR
(((tblPROJFHIST.PROJFH_AC_PER01310)<>[tblWMSNEW]![PROJFH_AC_PER1310]))
OR
(((tblPROJFHIST.PROJFH_FS_CD01230)<>[tblWMSNEW]![PROJFH_FS_CD1230])) OR
(((tblPROJFHIST.PROJFH_FS_PER01310)<>[tblWMSNEW]![PROJFH_FS_PER1310]))
OR
(((tblPROJFHIST.PROJFH_FC_SS011XX)<>[tblWMSNEW]![PROJFH_FC_SS11XX])) OR
(((tblPROJFHIST.PROJFH_FC_CD01230)<>[tblWMSNEW]![PROJFH_FC_CD1230])) OR
(((tblPROJFHIST.PROJFH_FC_PER01310)<>[tblWMSNEW]![PROJFH_FC_PER1310]))
OR
(((tblPROJFHIST.PROJFH_AS_SS011XX)<>[tblWMSNEW]![PROJFH_AS_SS11XX])) OR
(((tblPROJFHIST.PROJFH_AS_CD01230)<>[tblWMSNEW]![PROJFH_AS_CD1230])) OR
(((tblPROJFHIST.PROJFH_AS_PER01310)<>[tblWMSNEW]![PROJFH_AS_PER1310]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top