inserting records using a query

[grace]

Hello, I have te following fields (showing some sample
data)

[JobCode] [AuthStatus] [FTPTStatus] [Headcnt]
1 Atty A FT 2
1 Atty A PT 1
1 Para A FT 3
1 Admin F FT 1

Using the above fields, there can be up to 15
combinations of [JobCode], [AuthStatus], and
[FTPTStatus]. The table I have was exported from an
Excel spreadsheet. I need to insert records into this
table where a combination does not exist and assign a
[Headcnt] of "0". For example, if

1, does not
have a Para, A, PT combination, I need to add a record
with that combination and assign a Headcnt of "0". How
can I query for records that are null in those three
fields and the insert a new reocrd with those assignments.

Hope this makes sense,

Thank you, Grace​

 

[John Vinson]

Hello, I have te following fields (showing some sample
data)

[JobCode] [AuthStatus] [FTPTStatus] [Headcnt]
1 Atty A FT 2
1 Atty A PT 1
1 Para A FT 3
1 Admin F FT 1

Using the above fields, there can be up to 15
combinations of [JobCode], [AuthStatus], and
[FTPTStatus]. The table I have was exported from an
Excel spreadsheet. I need to insert records into this
table where a combination does not exist and assign a
[Headcnt] of "0". For example, if

1, does not
have a Para, A, PT combination, I need to add a record
with that combination and assign a Headcnt of "0". How
can I query for records that are null in those three
fields and the insert a new reocrd with those assignments.​

Well, the values of those fields aren't NULL - the records simply do
not exist!

What you'll probably need to do is create a table with all the allowed
combinations of JobCode, AuthStatus, and FTPTStatus. Create a Query
joining this new table to the table above, joining on all three
fields; select each Join line and choose Option 2 (or 3) - "show all
records in <new table> and matching records in <your table>".

Put a calculated field in the query defined as

ExpHeadcnt: NZ([headcnt])

For records that do not exist in your table, the headcnt value in the
Outer Join query will be NULL; this function will convert that NULL to
a zero for export to Excel.​

 

[grace]

Thanks for the information. You are right the records
don't exist. Is there an quick way to create the new
table that contains the combinations of JobCode,
AuthStatus, and FTPT STatus? There arer 15 records per
center and there are 150 centers. Can I use a Make Table
query to do this?

Thanks, Grace

-----Original Message-----

Hello, I have te following fields (showing some sample
data)

[JobCode] [AuthStatus] [FTPTStatus] [Headcnt]
1 Atty A FT 2
1 Atty A PT 1
1 Para A FT 3
1 Admin F FT 1

Using the above fields, there can be up to 15
combinations of [JobCode], [AuthStatus], and
[FTPTStatus]. The table I have was exported from an
Excel spreadsheet. I need to insert records into this
table where a combination does not exist and assign a
[Headcnt] of "0". For example, if

1, does not
have a Para, A, PT combination, I need to add a record
with that combination and assign a Headcnt of "0". How
can I query for records that are null in those three
fields and the insert a new reocrd with those​

assignments.

Well, the values of those fields aren't NULL - the records simply do
not exist!

What you'll probably need to do is create a table with all the allowed
combinations of JobCode, AuthStatus, and FTPTStatus. Create a Query
joining this new table to the table above, joining on all three
fields; select each Join line and choose Option 2 (or 3) - "show all
records in <new table> and matching records in <your table>".

Put a calculated field in the query defined as

ExpHeadcnt: NZ([headcnt])

For records that do not exist in your table, the headcnt value in the
Outer Join query will be NULL; this function will convert that NULL to
a zero for export to Excel.


.
​

​

 

[John Vinson]

Thanks for the information. You are right the records
don't exist. Is there an quick way to create the new
table that contains the combinations of JobCode,
AuthStatus, and FTPT STatus? There arer 15 records per
center and there are 150 centers. Can I use a Make Table
query to do this?

The table I suggest should not have any Center information at all:
just the fifteen rows. You could create a MakeTable to put them in,
but it would take five times as long as just typing in fifteen rows!