concatenating with a append query

[Guest]

I have a database that is used to archive old data from 5 other database.
All of the databases have a field "dbid", including the archive database,
which is defaulted when the user adds a new entry. I have a button on the
main for of the database that appends to the archived database and deletes it
from the current database. I would like to have the "dbid" from the current
database concatenated to "dbid" of the archive database, when it is appended
how can I do this in my append query?
Thank you..

 

[Jeff Boyce]

Raj

I'm not completely clear on what you are trying to do. It sounds like you
want to put the "dbid" from the "other" databases in your "archive"
database. Won't that mean you could have 5 identical dbid's?

 

[Guest]

Jeff,
What I am trying to do is add the old dbid to the acrchive dbid, so that it
is now showing as archived but also shows from which database it came from.
There has been incidence where user need to know what the originating
database.
The easies thing would be for me to add a new field to the database but I
have several users that have queries build this information and getting the
to update they queries and reports will be a major hassle. I am hoping this
will be the lessor of the two evils if it is possible.
Thanks
Raj

 

[John Spencer (MVP)]

Could you post the SQL statement you are using to do the append?

INSERT INTO TargetTableName
(dbID, field1, field2, Field3, ...)
SELECT "MyDBIDvalue", S.Field1, S.Field2, S.Field3
FROM SourceTableName AS S

If you DBID is a number field then you don't need the quotes around whatever the
db value is. A

 

[Guest]

John,
Here is the SQL :
INSERT INTO [Active Archived] ( circuitid, customer_account, project_name,
sub_project, sub_project_1, business_unit, ratm_contact, research_completed,
handoff_date, followup_date, nextfollowup_date, fixing_dept, fixing_name,
sales_rep, date_resolved, rev_comment, mrc_revenue, nrc_revenue, supportDI,
bill_cycle, monthinyear, mso_number, misc_comment, root_cause, eckkt, vendor,
fc_handoff, fc_hold, fc_datesent, fc_costsavings, fc_comment, fc_comment1,
bam_ticket_no, bam_status, bam_dollars, mrc_billing_stopped, credit_comment,
business_line, product_type, product_tier, access_type, Customer_Name,
status, control_id, Teoco_date, rastatus, category, sales_order_id,
oe_order_id, service_cat_code, invoiced, Fix, IDTag, dbId )
SELECT MasterData.circuitid, MasterData.customer_account,
MasterData.project_name, MasterData.sub_project, MasterData.sub_project_1,
MasterData.business_unit, MasterData.ratm_contact,
MasterData.research_completed, MasterData.handoff_date,
MasterData.followup_date, MasterData.nextfollowup_date,
MasterData.fixing_dept, MasterData.fixing_name, MasterData.sales_rep,
MasterData.date_resolved, MasterData.rev_comment, MasterData.mrc_revenue,
MasterData.nrc_revenue, MasterData.supportDI, MasterData.bill_cycle,
MasterData.monthinyear, MasterData.mso_number, MasterData.misc_comment,
MasterData.root_cause, MasterData.eckkt, MasterData.vendor,
MasterData.fc_handoff, MasterData.fc_hold, MasterData.fc_datesent,
MasterData.fc_costsavings, MasterData.fc_comment, MasterData.fc_comment1,
MasterData.bam_ticket_no, MasterData.bam_status, MasterData.bam_dollars,
MasterData.mrc_billing_stopped, MasterData.credit_comment,
MasterData.business_line, MasterData.product_type, MasterData.product_tier,
MasterData.access_type, MasterData.Customer_Name, MasterData.status,
MasterData.control_id, MasterData.Teoco_date, MasterData.rastatus,
MasterData.category, MasterData.sales_order_id, MasterData.oe_order_id,
MasterData.service_cat_code, MasterData.invoiced, MasterData.Fix,
MasterData.IDTag, MasterData.dbId
FROM MasterData
WHERE (((MasterData.circuitid)=[forms]![frmMain]![circuitid]));

 

[Guest]

john,
The dbid is a text field.

Could you post the SQL statement you are using to do the append?

INSERT INTO TargetTableName
(dbID, field1, field2, Field3, ...)
SELECT "MyDBIDvalue", S.Field1, S.Field2, S.Field3
FROM SourceTableName AS S

If you DBID is a number field then you don't need the quotes around whatever the
db value is. A

 

[Jeff Boyce]

Raj

Unless your "archive" database includes that "which database?" field, it
would not be able to tell the id's apart.

Are you saying that the users have queries they use to find data on the
archived data system (or on their own systems)?

Adding a field to a table in your archived system won't change any queries
in the individual system.

Perhaps there's another approach...

If the individual systems need to be able to archive, AND if there's no need
to "share" archive information across all the individual systems, don't
bother creating an archive. Just add a field in the individual systems'
table that indicates that a record is archived. A "Yes/No" field, if you
only need to know that, or a Date/Time field, if you need to know when.

 

[John Spencer (MVP)]

Looking at it, I would say that will work as long as MasterDate.dbID has a
value. Does it?

IF not then you could just replace MasterData.dbId with a specific text value
for each source database. Something like

Insert INTO ... DbID)
SELECT ..., MasterData.IdTag, "DataBaseOne"
FROM MasterData
WHERE ...

Then for the next one

Insert INTO ... DbID)
SELECT ..., MasterData.IdTag, "DataBaseTwo"
FROM MasterData
WHERE ...

John,
Here is the SQL :
INSERT INTO [Active Archived] ( circuitid, customer_account, project_name,
sub_project, sub_project_1, business_unit, ratm_contact, research_completed,
handoff_date, followup_date, nextfollowup_date, fixing_dept, fixing_name,
sales_rep, date_resolved, rev_comment, mrc_revenue, nrc_revenue, supportDI,
bill_cycle, monthinyear, mso_number, misc_comment, root_cause, eckkt, vendor,
fc_handoff, fc_hold, fc_datesent, fc_costsavings, fc_comment, fc_comment1,
bam_ticket_no, bam_status, bam_dollars, mrc_billing_stopped, credit_comment,
business_line, product_type, product_tier, access_type, Customer_Name,
status, control_id, Teoco_date, rastatus, category, sales_order_id,
oe_order_id, service_cat_code, invoiced, Fix, IDTag, dbId )
SELECT MasterData.circuitid, MasterData.customer_account,
MasterData.project_name, MasterData.sub_project, MasterData.sub_project_1,
MasterData.business_unit, MasterData.ratm_contact,
MasterData.research_completed, MasterData.handoff_date,
MasterData.followup_date, MasterData.nextfollowup_date,
MasterData.fixing_dept, MasterData.fixing_name, MasterData.sales_rep,
MasterData.date_resolved, MasterData.rev_comment, MasterData.mrc_revenue,
MasterData.nrc_revenue, MasterData.supportDI, MasterData.bill_cycle,
MasterData.monthinyear, MasterData.mso_number, MasterData.misc_comment,
MasterData.root_cause, MasterData.eckkt, MasterData.vendor,
MasterData.fc_handoff, MasterData.fc_hold, MasterData.fc_datesent,
MasterData.fc_costsavings, MasterData.fc_comment, MasterData.fc_comment1,
MasterData.bam_ticket_no, MasterData.bam_status, MasterData.bam_dollars,
MasterData.mrc_billing_stopped, MasterData.credit_comment,
MasterData.business_line, MasterData.product_type, MasterData.product_tier,
MasterData.access_type, MasterData.Customer_Name, MasterData.status,
MasterData.control_id, MasterData.Teoco_date, MasterData.rastatus,
MasterData.category, MasterData.sales_order_id, MasterData.oe_order_id,
MasterData.service_cat_code, MasterData.invoiced, MasterData.Fix,
MasterData.IDTag, MasterData.dbId
FROM MasterData
WHERE (((MasterData.circuitid)=[forms]![frmMain]![circuitid]));

Could you post the SQL statement you are using to do the append?

INSERT INTO TargetTableName
(dbID, field1, field2, Field3, ...)
SELECT "MyDBIDvalue", S.Field1, S.Field2, S.Field3
FROM SourceTableName AS S

If you DBID is a number field then you don't need the quotes around whatever the
db value is. A