Add a field for query output only

[heidibbb]

Hello,
I have a query joining two tables, where the output file will be uploaded into our CRM system.

CRM system needs specific a specific layout, which includes fields not in the two tables that I'm importing.

I'd like to add a field into my query output that is an "ask for". For example, add the field of "Campaign Name", I would get a prompt to enter text for the campaign name, and that campaign name would be on all the records in the output file.

 

[Joe4]

One way is to create a one record table with this field that you want, and link this table to your query (actually, if it just a one record table, you don't even need to set up a link/relationship to the other tables in your query, just add it to the query without a relationship).

You could then set-up a Form based on this one record table where they can enter the value that they want. When I do this, I usually change the Properties of the Form to not allow and record additions or deletions (to ensure it will always be a one-record table).

 

[heidibbb]

This makes sense, but a follow-up question. So, I create the form for this one record table. Then, when I set up the query to output the data for our CRM system, will the form automatically show when I run the query?

Query design is basically this:
Table 1 - Name, address
Table 2 - Sales rep information

Table 1 & 2 are linked on state

table 3 - CRM description form (not linked)

Query output pulling in fields from tables 1, 2 and 3.

when I run the query, will the form automatically show or do i need to define that somewhere?

 

[Joe4]

No, what I suggest is setting up a Form with (at least) two objects on it.

1. A Text Box where they enter the value they want (Control Source is the one-record table).

2. A command button that they click that runs the query (or better yet, the export script that exports the data from the query).

Then use this Form for running this process.

 

[heidibbb]

Got it!!
thanks so much!!
Heidi

 

[heidibbb]

One last question on this... do you know if there is an action I can add that clears the data that I enter for the next export?

So I enter the campaign name of TradeShow1232011, run the action and get my file.

I want to now remove TradeShow1232011 from the form once I run the query.

 

[Joe4]

A simple Update Query should take care of that

Update TableName
Set FieldName=Null;