Need to get a field to repeat in a table or query

[Regi]

I have a table that I built a query from that has data from our main frame in
it. The data is for multiple jobs. There is a field called Fee Pct that only
comes over on the main task of a job. I need to repeat or copy this field to
it's sub tasks.

Ex: How it comes over now
Job # Sub Task Fee Pct
1180 000 13.00
1180 200 0.00
1180 210 0.00

What I need it to look like:
1180 000 13.00
1180 200 13.00
1180 210 13.00
How could I do this in either the Table or the Query?

Thanks

 

[Jerry Whittle]

There's a couple of ways: the right one and then the way that gets the job
done.

Getting the job done first. You could do an update query once the data is
imported. I'm assuming that a job number like 1180 will always have a Fee Pct
of 13.00. Replace Regi with the proper table name below. Also make a backup
first in case the results aren't what you want.

UPDATE Regi
SET Regi.[Fee Pct] = DMax("[Fee Pct]","[Regi]","[Job #] = " & [Job #]);

Now the proper, by the book, way of doing things would be to have two
tables. The main table would have the Job # as the primary key and any other
relevant data for the entire job such as the Fee Pct.

The other table would have the Sub Tasks. Each record would have the Sub
Task number and the Job # as the foriegn key to the main table. Now you could
join the two tables in a query to grab the Fee Pct info for the Sub Tasks.

Of course you would need to think this through plus devise a way to move the
data to different tables from the main frame file.

 

[Regi]

Thanks I'll give it a try.

There's a couple of ways: the right one and then the way that gets the job
done.

Getting the job done first. You could do an update query once the data is
imported. I'm assuming that a job number like 1180 will always have a Fee Pct
of 13.00. Replace Regi with the proper table name below. Also make a backup
first in case the results aren't what you want.

UPDATE Regi
SET Regi.[Fee Pct] = DMax("[Fee Pct]","[Regi]","[Job #] = " & [Job #]);

Now the proper, by the book, way of doing things would be to have two
tables. The main table would have the Job # as the primary key and any other
relevant data for the entire job such as the Fee Pct.

The other table would have the Sub Tasks. Each record would have the Sub
Task number and the Job # as the foriegn key to the main table. Now you could
join the two tables in a query to grab the Fee Pct info for the Sub Tasks.

Of course you would need to think this through plus devise a way to move the
data to different tables from the main frame file.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table that I built a query from that has data from our main frame in
it. The data is for multiple jobs. There is a field called Fee Pct that only
comes over on the main task of a job. I need to repeat or copy this field to
it's sub tasks.

Ex: How it comes over now
Job # Sub Task Fee Pct
1180 000 13.00
1180 200 0.00
1180 210 0.00

What I need it to look like:
1180 000 13.00
1180 200 13.00
1180 210 13.00
How could I do this in either the Table or the Query?

Thanks

 

[Regi]

Sorry Jerry, I'm lost. I've never done an Update Query before and I got the
Help from Miscrosoft help online but I still don't get it. I can't seem to
figure out what goes where. Regi

There's a couple of ways: the right one and then the way that gets the job
done.

Getting the job done first. You could do an update query once the data is
imported. I'm assuming that a job number like 1180 will always have a Fee Pct
of 13.00. Replace Regi with the proper table name below. Also make a backup
first in case the results aren't what you want.

UPDATE Regi
SET Regi.[Fee Pct] = DMax("[Fee Pct]","[Regi]","[Job #] = " & [Job #]);

Now the proper, by the book, way of doing things would be to have two
tables. The main table would have the Job # as the primary key and any other
relevant data for the entire job such as the Fee Pct.

The other table would have the Sub Tasks. Each record would have the Sub
Task number and the Job # as the foriegn key to the main table. Now you could
join the two tables in a query to grab the Fee Pct info for the Sub Tasks.

Of course you would need to think this through plus devise a way to move the
data to different tables from the main frame file.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table that I built a query from that has data from our main frame in
it. The data is for multiple jobs. There is a field called Fee Pct that only
comes over on the main task of a job. I need to repeat or copy this field to
it's sub tasks.

Ex: How it comes over now
Job # Sub Task Fee Pct
1180 000 13.00
1180 200 0.00
1180 210 0.00

What I need it to look like:
1180 000 13.00
1180 200 13.00
1180 210 13.00
How could I do this in either the Table or the Query?

Thanks

 

[Jerry Whittle]

Hi Regi,

What I sent you is know as a SQL (Structured Query Language) statement. If
you create a query with Access, it creates the SQL statement in the
background.

Try this: First make a backup of the table or entire database.
Open a new query in Design View. Don't select a table.
Next go to View, SQL View. Copy and paste in the statement below.
Change the table name I used to the actual table name.
Then run the query. A warning should pop up that you're about to update X
number of records. OK.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry Jerry, I'm lost. I've never done an Update Query before and I got the
Help from Miscrosoft help online but I still don't get it. I can't seem to
figure out what goes where. Regi

There's a couple of ways: the right one and then the way that gets the job
done.

Getting the job done first. You could do an update query once the data is
imported. I'm assuming that a job number like 1180 will always have a Fee Pct
of 13.00. Replace Regi with the proper table name below. Also make a backup
first in case the results aren't what you want.

UPDATE Regi
SET Regi.[Fee Pct] = DMax("[Fee Pct]","[Regi]","[Job #] = " & [Job #]);

Now the proper, by the book, way of doing things would be to have two
tables. The main table would have the Job # as the primary key and any other
relevant data for the entire job such as the Fee Pct.

The other table would have the Sub Tasks. Each record would have the Sub
Task number and the Job # as the foriegn key to the main table. Now you could
join the two tables in a query to grab the Fee Pct info for the Sub Tasks.

Of course you would need to think this through plus devise a way to move the
data to different tables from the main frame file.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table that I built a query from that has data from our main frame in
it. The data is for multiple jobs. There is a field called Fee Pct that only
comes over on the main task of a job. I need to repeat or copy this field to
it's sub tasks.

Ex: How it comes over now
Job # Sub Task Fee Pct
1180 000 13.00
1180 200 0.00
1180 210 0.00

What I need it to look like:
1180 000 13.00
1180 200 13.00
1180 210 13.00
How could I do this in either the Table or the Query?

Thanks

 

[Regi]

Thanks Jerry I'll try it!

Hi Regi,

What I sent you is know as a SQL (Structured Query Language) statement. If
you create a query with Access, it creates the SQL statement in the
background.

Try this: First make a backup of the table or entire database.
Open a new query in Design View. Don't select a table.
Next go to View, SQL View. Copy and paste in the statement below.
Change the table name I used to the actual table name.
Then run the query. A warning should pop up that you're about to update X
number of records. OK.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry Jerry, I'm lost. I've never done an Update Query before and I got the
Help from Miscrosoft help online but I still don't get it. I can't seem to
figure out what goes where. Regi

There's a couple of ways: the right one and then the way that gets the job
done.

Getting the job done first. You could do an update query once the data is
imported. I'm assuming that a job number like 1180 will always have a Fee Pct
of 13.00. Replace Regi with the proper table name below. Also make a backup
first in case the results aren't what you want.

UPDATE Regi
SET Regi.[Fee Pct] = DMax("[Fee Pct]","[Regi]","[Job #] = " & [Job #]);

Now the proper, by the book, way of doing things would be to have two
tables. The main table would have the Job # as the primary key and any other
relevant data for the entire job such as the Fee Pct.

The other table would have the Sub Tasks. Each record would have the Sub
Task number and the Job # as the foriegn key to the main table. Now you could
join the two tables in a query to grab the Fee Pct info for the Sub Tasks.

Of course you would need to think this through plus devise a way to move the
data to different tables from the main frame file.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a table that I built a query from that has data from our main frame in
it. The data is for multiple jobs. There is a field called Fee Pct that only
comes over on the main task of a job. I need to repeat or copy this field to
it's sub tasks.

Ex: How it comes over now
Job # Sub Task Fee Pct
1180 000 13.00
1180 200 0.00
1180 210 0.00

What I need it to look like:
1180 000 13.00
1180 200 13.00
1180 210 13.00
How could I do this in either the Table or the Query?

Thanks