Problem with Advanced Append Query

G

Guest

Hello all nice to see you again,

I am having a problem creating an append query to populate multiple records
from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit button
that runs the query. The query will take that date and create a record for
each task in a list (separate table).

I created an 3 expressions that point to the fields on the form to append to
the key fields and date field in the completed tasks table.





Code:
PARAMETERS Forms![NewJobSetup]![JDE Number] Long, Forms![NewJobSetup]![JDE
Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted] DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code], TaskName,
DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1,
[Forms]![NewJobSetup]![JDE Division Code] AS Expr2, [MasterTaskList]![ID] AS
Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON
(JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE
Division Code] = CompletionLog.[JDE Division Code])) ON MasterTaskList.ID =
CompletionLog.TaskName;



Any leads on where to check for problems would be appreciated.
 
J

Jeff Boyce

It sounds a little like you are trying to create (mostly) "blank"
placeholder records. I'm not sure if that's necessary in Access (because I
don't know the big picture of what you are trying to accomplish).
 
G

Guest

Hey Jeff,

I am trying to automate the process of repeated data entry for my users.
Instead of them having to manually create a record with each task with the
same date, I want them to enter the date once on the form and press a button
that would populate all the records with that date.

The query is simple up to the point of having to pull the list of tasks to
populate each record differently.

Hope that helps, and thank you.
Rob



Jeff Boyce said:
It sounds a little like you are trying to create (mostly) "blank"
placeholder records. I'm not sure if that's necessary in Access (because I
don't know the big picture of what you are trying to accomplish).

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
Hello all nice to see you again,

I am having a problem creating an append query to populate multiple records
from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit button
that runs the query. The query will take that date and create a record for
each task in a list (separate table).

I created an 3 expressions that point to the fields on the form to append to
the key fields and date field in the completed tasks table.





Code:
PARAMETERS Forms![NewJobSetup]![JDE Number] Long, Forms![NewJobSetup]![JDE
Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted] DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code], TaskName,
DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1,
[Forms]![NewJobSetup]![JDE Division Code] AS Expr2, [MasterTaskList]![ID] AS
Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON
(JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE
Division Code] = CompletionLog.[JDE Division Code])) ON MasterTaskList.ID =
CompletionLog.TaskName;



Any leads on where to check for problems would be appreciated.
Click to expand...
Click to expand...
 
J

Jeff Boyce

You ended your original post asking where to check for problems. Is the
code you created not working? Are you getting any error messages?

Another approach, if I understand what you are trying to do correctly, would
be to have the date entered used to (re-)set the field's DefaultValue
property. That way, the next record started to be entered would already
have a date value. While it could be overwritten, it would save keystrokes
for all those new records with the same date.

Or am I still missing your point?

--
Regards

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
Hey Jeff,

I am trying to automate the process of repeated data entry for my users.
Instead of them having to manually create a record with each task with the
same date, I want them to enter the date once on the form and press a button
that would populate all the records with that date.

The query is simple up to the point of having to pull the list of tasks to
populate each record differently.

Hope that helps, and thank you.
Rob



Jeff Boyce said:
It sounds a little like you are trying to create (mostly) "blank"
placeholder records. I'm not sure if that's necessary in Access (because I
don't know the big picture of what you are trying to accomplish).

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
Hello all nice to see you again,

I am having a problem creating an append query to populate multiple records
from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit button
that runs the query. The query will take that date and create a record for
each task in a list (separate table).

I created an 3 expressions that point to the fields on the form to
Click to expand...
append
to
the key fields and date field in the completed tasks table.





Code:
PARAMETERS Forms![NewJobSetup]![JDE Number] Long, Forms![NewJobSetup]![JDE
Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted] DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code], TaskName,
DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1,
[Forms]![NewJobSetup]![JDE Division Code] AS Expr2,
Click to expand...
[MasterTaskList]![ID]
AS
Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON
(JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE
Division Code] = CompletionLog.[JDE Division Code])) ON
Click to expand...
MasterTaskList.ID
=
CompletionLog.TaskName;



Any leads on where to check for problems would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am not getting any results when I run the query.

Here are my tables



JobTable CompletionLog MasterTaskList

JDE Division Code JDE Division Code ID
JDE Number JDE Number TaskName
Master Plan TaskName
Community DateComplete
City
# Homesites
DateCommitted


I want to create records in the completion log table for every record in the
MasterTaskList table. The remaining fields in the completion log will come
from the info on the form that the user set up on the job table.



--
Thanks,

Rob


Jeff Boyce said:
You ended your original post asking where to check for problems. Is the
code you created not working? Are you getting any error messages?

Another approach, if I understand what you are trying to do correctly, would
be to have the date entered used to (re-)set the field's DefaultValue
property. That way, the next record started to be entered would already
have a date value. While it could be overwritten, it would save keystrokes
for all those new records with the same date.

Or am I still missing your point?

--
Regards

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
Hey Jeff,

I am trying to automate the process of repeated data entry for my users.
Instead of them having to manually create a record with each task with the
same date, I want them to enter the date once on the form and press a button
that would populate all the records with that date.

The query is simple up to the point of having to pull the list of tasks to
populate each record differently.

Hope that helps, and thank you.
Rob



Jeff Boyce said:
It sounds a little like you are trying to create (mostly) "blank"
placeholder records. I'm not sure if that's necessary in Access (because I
don't know the big picture of what you are trying to accomplish).

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Hello all nice to see you again,

I am having a problem creating an append query to populate multiple
records
from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit button
that runs the query. The query will take that date and create a record for
each task in a list (separate table).

I created an 3 expressions that point to the fields on the form to append
to
the key fields and date field in the completed tasks table.





Code:
PARAMETERS Forms![NewJobSetup]![JDE Number] Long, Forms![NewJobSetup]![JDE
Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted] DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code], TaskName,
DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1,
[Forms]![NewJobSetup]![JDE Division Code] AS Expr2, [MasterTaskList]![ID]
AS
Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON
(JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE
Division Code] = CompletionLog.[JDE Division Code])) ON MasterTaskList.ID
=
CompletionLog.TaskName;



Any leads on where to check for problems would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Not getting any results ...

Does that mean zero rows returned, or the query does nothing when you click
it?

If the former, perhaps there are no rows matching the criteria?

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
I am not getting any results when I run the query.

Here are my tables



JobTable CompletionLog MasterTaskList

JDE Division Code JDE Division Code ID
JDE Number JDE Number TaskName
Master Plan TaskName
Community DateComplete
City
# Homesites
DateCommitted


I want to create records in the completion log table for every record in the
MasterTaskList table. The remaining fields in the completion log will come
from the info on the form that the user set up on the job table.



--
Thanks,

Rob


Jeff Boyce said:
You ended your original post asking where to check for problems. Is the
code you created not working? Are you getting any error messages?

Another approach, if I understand what you are trying to do correctly, would
be to have the date entered used to (re-)set the field's DefaultValue
property. That way, the next record started to be entered would already
have a date value. While it could be overwritten, it would save keystrokes
for all those new records with the same date.

Or am I still missing your point?

--
Regards

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
Hey Jeff,

I am trying to automate the process of repeated data entry for my users.
Instead of them having to manually create a record with each task with the
same date, I want them to enter the date once on the form and press a button
that would populate all the records with that date.

The query is simple up to the point of having to pull the list of tasks to
populate each record differently.

Hope that helps, and thank you.
Rob



:

It sounds a little like you are trying to create (mostly) "blank"
placeholder records. I'm not sure if that's necessary in Access (because I
don't know the big picture of what you are trying to accomplish).

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Hello all nice to see you again,

I am having a problem creating an append query to populate multiple
records
from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit button
that runs the query. The query will take that date and create a
Click to expand...
record
for
each task in a list (separate table).

I created an 3 expressions that point to the fields on the form to append
to
the key fields and date field in the completed tasks table.





Code:
PARAMETERS Forms![NewJobSetup]![JDE Number] Long, Forms![NewJobSetup]![JDE
Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted] DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code], TaskName,
DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1,
[Forms]![NewJobSetup]![JDE Division Code] AS Expr2, [MasterTaskList]![ID]
AS
Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON
(JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND (JobTable.[JDE
Division Code] = CompletionLog.[JDE Division Code])) ON MasterTaskList.ID
=
CompletionLog.TaskName;



Any leads on where to check for problems would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

yes that means zero records retunred. That is why I am wrote the post.
--
Thanks,

Rob


Jeff Boyce said:
Not getting any results ...

Does that mean zero rows returned, or the query does nothing when you click
it?

If the former, perhaps there are no rows matching the criteria?

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
I am not getting any results when I run the query.

Here are my tables



JobTable CompletionLog MasterTaskList

JDE Division Code JDE Division Code ID
JDE Number JDE Number TaskName
Master Plan TaskName
Community DateComplete
City
# Homesites
DateCommitted


I want to create records in the completion log table for every record in the
MasterTaskList table. The remaining fields in the completion log will come
from the info on the form that the user set up on the job table.



--
Thanks,

Rob


Jeff Boyce said:
You ended your original post asking where to check for problems. Is the
code you created not working? Are you getting any error messages?

Another approach, if I understand what you are trying to do correctly, would
be to have the date entered used to (re-)set the field's DefaultValue
property. That way, the next record started to be entered would already
have a date value. While it could be overwritten, it would save keystrokes
for all those new records with the same date.

Or am I still missing your point?

--
Regards

Jeff Boyce
<Office/Access MVP>

Hey Jeff,

I am trying to automate the process of repeated data entry for my users.
Instead of them having to manually create a record with each task with the
same date, I want them to enter the date once on the form and press a
button
that would populate all the records with that date.

The query is simple up to the point of having to pull the list of tasks to
populate each record differently.

Hope that helps, and thank you.
Rob



:

It sounds a little like you are trying to create (mostly) "blank"
placeholder records. I'm not sure if that's necessary in Access
(because I
don't know the big picture of what you are trying to accomplish).

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Hello all nice to see you again,

I am having a problem creating an append query to populate multiple
records
from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit
button
that runs the query. The query will take that date and create a record
for
each task in a list (separate table).

I created an 3 expressions that point to the fields on the form to
append
to
the key fields and date field in the completed tasks table.





Code:
PARAMETERS Forms![NewJobSetup]![JDE Number] Long,
Forms![NewJobSetup]![JDE
Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted]
DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code],
TaskName,
DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1,
[Forms]![NewJobSetup]![JDE Division Code] AS Expr2,
[MasterTaskList]![ID]
AS
Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN CompletionLog ON
(JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND
(JobTable.[JDE
Division Code] = CompletionLog.[JDE Division Code])) ON
MasterTaskList.ID
=
CompletionLog.TaskName;



Any leads on where to check for problems would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Rob

I would approach this by stepping back...

Can you create a select query with parameters hard-coded in that generates
the rows you expect? Can you convert that to an append query that does what
you expect?

Once these steps were working, I'd move on to parameterizing it.

JOPO (just one person's opinion)

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
yes that means zero records retunred. That is why I am wrote the post.
--
Thanks,

Rob


Jeff Boyce said:
Not getting any results ...

Does that mean zero rows returned, or the query does nothing when you click
it?

If the former, perhaps there are no rows matching the criteria?

Jeff Boyce
<Office/Access MVP>

Robnauticus said:
I am not getting any results when I run the query.

Here are my tables



JobTable CompletionLog MasterTaskList

JDE Division Code JDE Division Code ID
JDE Number JDE Number TaskName
Master Plan TaskName
Community DateComplete
City
# Homesites
DateCommitted


I want to create records in the completion log table for every record
Click to expand...
in
the
MasterTaskList table. The remaining fields in the completion log will come
from the info on the form that the user set up on the job table.



--
Thanks,

Rob


:

You ended your original post asking where to check for problems. Is the
code you created not working? Are you getting any error messages?

Another approach, if I understand what you are trying to do
Click to expand...
correctly,
would
be to have the date entered used to (re-)set the field's DefaultValue
property. That way, the next record started to be entered would already
have a date value. While it could be overwritten, it would save keystrokes
for all those new records with the same date.

Or am I still missing your point?

--
Regards

Jeff Boyce
<Office/Access MVP>

Hey Jeff,

I am trying to automate the process of repeated data entry for my users.
Instead of them having to manually create a record with each task
Click to expand...
with
the
same date, I want them to enter the date once on the form and press a
button
that would populate all the records with that date.

The query is simple up to the point of having to pull the list of tasks to
populate each record differently.

Hope that helps, and thank you.
Rob



:

It sounds a little like you are trying to create (mostly) "blank"
placeholder records. I'm not sure if that's necessary in Access
(because I
don't know the big picture of what you are trying to accomplish).

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Hello all nice to see you again,

I am having a problem creating an append query to populate multiple
records
from input on a form. The field on the form is a date field.

To sum it up, the user will type in the date and click the commit
button
that runs the query. The query will take that date and create
Click to expand...
a
record
for
each task in a list (separate table).

I created an 3 expressions that point to the fields on the form to
append
to
the key fields and date field in the completed tasks table.





Code:
PARAMETERS Forms![NewJobSetup]![JDE Number] Long,
Forms![NewJobSetup]![JDE
Division Code] Text ( 255 ), Forms![NewJobSetup]![DateCommitted]
DateTime;

INSERT INTO CompletionLog ( [JDE Number], [JDE Division Code],
TaskName,
DateComplete )

SELECT [Forms]![NewJobSetup]![JDE Number] AS Expr1,
[Forms]![NewJobSetup]![JDE Division Code] AS Expr2,
[MasterTaskList]![ID]
AS
Expr3, [Forms]![NewJobSetup]![DateCommitted] AS Expr4

FROM MasterTaskList INNER JOIN (JobTable INNER JOIN
Click to expand...
CompletionLog
ON
(JobTable.[JDE Number] = CompletionLog.[JDE Number]) AND
(JobTable.[JDE
Division Code] = CompletionLog.[JDE Division Code])) ON
MasterTaskList.ID
=
CompletionLog.TaskName;



Any leads on where to check for problems would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error message instead of parameter box appearing 1
Problem with Update Query 5
AutoNumber Field Not Populating with Append Query 2
Access Determine what query to run based on a checkbox 0
Improving append query speed 1
help understanding a query 2
Access Append Query Failure 1
Append Query Date Criteria Problem. 13

Top