Combination of fields to create records

  • Thread starter Thread starter atledreier
  • Start date Start date
A

atledreier

Hello.

I have the following problem.

I have a master list of tags with their description and other
information.
Two of these information fields is 'Loop' and 'Area'. I then combine
these two in a query into an expression field that is [loop] & "-" &
[Area]. This works fine. I also made a query that list only the unique
combinations of Loop and Area.

Now, my problem:
These unique combinations of 'Loop' and 'Area' need to go in the
master list, as a tag.
I have made an append-query that can add them to the master table with
the correct category, but that will not delete combinations no longer
used.

Is there a way to make sure this master list of tags always contain
only relevant combinations of the fields? Or do I need to make my
users update this manually at regular intervals to make sure the list
is correct?


TBL_Tag:
Tag
Desc
Loop
Area
Cat
 
IF the ONLY records that should be in the master list are the ones from the
append query, then DELETE all the records from the Master list BEFORE you
append the records.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
If you can create an append query to produce all the required records, you
really shouldn't have another table. Instead you should covert the append
query to a select query and use it instead of the table.

Another option might be to delete all the records in the table then
repopulated it with new data in the append query.

You could also change the Append query to a Make Table and just recreate the
table as needed.

Another option is to use the select statement in the Append query as a
sub-query to remove any unneeded records then repopulate the table with the
needed data.

If your Append query as all the records needed in the table, my first idea
of using a select query instead is best.

Test then test some more before doing any of the above.
 
I have thought about the append query, and the 'make table'

The main table looks like this (abbreviated, I have more irrelevant
fields):

TBL_Tag:
*Tag
Desc
Loop
Area
Cat

This table changes constantly.
What I need is to get all the unique combinations of Loop - Area in as
'Tag' entries with category="Loop", and I need to know that the loop-
tags in the table is reasonably recent. I could run an update every
time the database is opened or something. Also, the loop-tags need a
description field, and I think if I go for the destroy-rebuild
approach i will lose the descriptions for all the loop-tags. If I
append any new ones I will retain the descriptions I have created and
only have to add new ones once.

I think I will try the 'append' and 'delete' approach. Append any new
ones, and delete any in TBL_tag that don't exist in the select query.

Any tips on when to run these queries? Any pitfalls in a multi-user
environment?

- Atle


If you can create an append query to produce all the required records, you
really shouldn't have another table. Instead you should covert the append
query to a select query and use it instead of the table.

Another option might be to delete all the records in the table then
repopulated it with new data in the append query.

You could also change the Append query to a Make Table and just recreate the
table as needed.

Another option is to use the select statement in the Append query as a
sub-query to remove any unneeded records then repopulate the table with the
needed data.

If your Append query as all the records needed in the table, my first idea
of using a select query instead is best.

Test then test some more before doing any of the above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



atledreier said:
Hello.
Click to expand...
I have the following problem.
Click to expand...
I have a master list of tags with their description and other
information.
Two of these information fields is 'Loop' and 'Area'. I then combine
these two in a query into an expression field that is [loop] & "-" &
[Area]. This works fine. I also made a query that list only the unique
combinations of Loop and Area.
Click to expand...
Now, my problem:
These unique combinations of 'Loop' and 'Area' need to go in the
master list, as a tag.
I have made an append-query that can add them to the master table with
the correct category, but that will not delete combinations no longer
used.
Click to expand...
Is there a way to make sure this master list of tags always contain
only relevant combinations of the fields? Or do I need to make my
users update this manually at regular intervals to make sure the list
is correct?

.
Click to expand...
Click to expand...
 
If I were doing this I would have a small - One record - table in the backend
that recorded the last update date. Then I would only append and delete once
per day by checking that field before running the process.

INSERT INTO Tbl_Tag (<<list of fields>>)
SELECT <<List of Fields>>
FROM SourceTable As S LEFT JOIN tbl_Tag As T
ON S.Loop = T.Loop
AND S.Area = T.Area
WHERE T.Loop is Null

DELETE *
FROM Tbl_Tag
WHERE TAG & "/" & Loop IN
(SELECT t.Tag & "/" & t.Loop
FROM SourceTable As S RIGHT JOIN tbl_Tag As T
ON S.Loop = T.Loop
AND S.Area = T.Area
WHERE s.Loop is Null)

WARNING: BACKUP your database BEFORE you attempt the DELETE query. I would
actually run the subquery as a separate query just to see which records would
be deleted. If it is selecting the correct records to delete then I would
feel more confident about the DELETE query. Once things were working
correctly then I would

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have thought about the append query, and the 'make table'

The main table looks like this (abbreviated, I have more irrelevant
fields):

TBL_Tag:
*Tag
Desc
Loop
Area
Cat

This table changes constantly.
What I need is to get all the unique combinations of Loop - Area in as
'Tag' entries with category="Loop", and I need to know that the loop-
tags in the table is reasonably recent. I could run an update every
time the database is opened or something. Also, the loop-tags need a
description field, and I think if I go for the destroy-rebuild
approach i will lose the descriptions for all the loop-tags. If I
append any new ones I will retain the descriptions I have created and
only have to add new ones once.

I think I will try the 'append' and 'delete' approach. Append any new
ones, and delete any in TBL_tag that don't exist in the select query.

Any tips on when to run these queries? Any pitfalls in a multi-user
environment?

- Atle


If you can create an append query to produce all the required records, you
really shouldn't have another table. Instead you should covert the append
query to a select query and use it instead of the table.

Another option might be to delete all the records in the table then
repopulated it with new data in the append query.

You could also change the Append query to a Make Table and just recreate the
table as needed.

Another option is to use the select statement in the Append query as a
sub-query to remove any unneeded records then repopulate the table with the
needed data.

If your Append query as all the records needed in the table, my first idea
of using a select query instead is best.

Test then test some more before doing any of the above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



atledreier said:
Hello.
I have the following problem.
I have a master list of tags with their description and other
information.
Two of these information fields is 'Loop' and 'Area'. I then combine
these two in a query into an expression field that is [loop] & "-" &
[Area]. This works fine. I also made a query that list only the unique
combinations of Loop and Area.
Now, my problem:
These unique combinations of 'Loop' and 'Area' need to go in the
master list, as a tag.
I have made an append-query that can add them to the master table with
the correct category, but that will not delete combinations no longer
used.
Is there a way to make sure this master list of tags always contain
only relevant combinations of the fields? Or do I need to make my
users update this manually at regular intervals to make sure the list
is correct?
TBL_Tag:
Tag
Desc
Loop
Area
Cat
.
Click to expand...
Click to expand...
Click to expand...
 
Thank you, John!

I already have a daily backup-function, I can just add the update to
that.

- Atle

If I were doing this I would have a small - One record - table in the backend
that recorded the last update date.  Then I would only append and delete once
per day by checking that field before running the process.

INSERT INTO Tbl_Tag (<<list of fields>>)
SELECT <<List of Fields>>
FROM SourceTable  As S LEFT JOIN tbl_Tag As T
ON S.Loop = T.Loop
AND S.Area = T.Area
WHERE T.Loop is Null

DELETE *
FROM Tbl_Tag
WHERE TAG & "/" & Loop IN
   (SELECT t.Tag & "/" & t.Loop
    FROM SourceTable  As S RIGHT JOIN tbl_Tag As T
    ON S.Loop = T.Loop
    AND S.Area = T.Area
    WHERE s.Loop is Null)

WARNING: BACKUP your database BEFORE you attempt the DELETE query.  I would
actually run the subquery as a separate query just to see which records would
be deleted.  If it is selecting the correct records to delete then I would
feel more confident about the DELETE query.  Once things were working
correctly then I would

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County


I have thought about the append query, and the 'make table'
Click to expand...
The main table looks like this (abbreviated, I have more irrelevant
fields):

This table changes constantly.
What I need is to get all the unique combinations of Loop - Area in as
'Tag' entries with category="Loop", and I need to know that the loop-
tags in the table is reasonably recent. I could run an update every
time the database is opened or something. Also, the loop-tags need a
description field, and I think if I go for the destroy-rebuild
approach i will lose the descriptions for all the loop-tags. If I
append any new ones I will retain the descriptions I have created and
only have to add new ones once.
Click to expand...
I think I will try the 'append' and 'delete' approach. Append any new
ones, and delete any in TBL_tag that don't exist in the select query.
Click to expand...
Any tips on when to run these queries? Any pitfalls in a multi-user
environment?
Click to expand...
- Atle
If you can create an append query to produce all the required records,you
really shouldn't have another table. Instead you should covert the append
query to a select query and use it instead of the table.
Another option might be to delete all the records in the table then
repopulated it with new data in the append query.
You could also change the Append query to a Make Table and just recreate the
table as needed.
Another option is to use the select statement in the Append query as a
sub-query to remove any unneeded records then repopulate the table with the
needed data.
If your Append query as all the records needed in the table, my first idea
of using a select query instead is best.
Test then test some more before doing any of the above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Hello.
I have the following problem.
I have a master list of tags with their description and other
information.
Two of these information fields is 'Loop' and 'Area'. I then combine
these two in a query into an expression field that is [loop] & "-" &
[Area]. This works fine. I also made a query that list only the unique
combinations of Loop and Area.
Now, my problem:
These unique combinations of 'Loop' and 'Area' need to go in the
master list, as a tag.
I have made an append-query that can add them to the master table with
the correct category, but that will not delete combinations no longer
used.
Is there a way to make sure this master list of tags always contain
only relevant combinations of the fields? Or do I need to make my
users update this manually at regular intervals to make sure the list
is correct?
TBL_Tag:
Tag
Desc
Loop
Area
Cat
.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top