Adding Criteria to Append Query (1 Viewer)

phenley

Registered User.
Local time
Today, 04:41
Joined
Jun 15, 2017
Messages
10
Hello,

I am building an Append query and I am trying to add a way to have user added criteria to the query. I tried uploading a picture of the relationships I have, but had trouble uploading the picture, so I will try to describe it.

In a table called the "Building Table" I have the field "States", "Data Date" and then a couple of other fields holding data. In another table called "Regions", I have a list of the states as a field, and then another field called "Region", which puts the states each in a region (North, East, South, West). States is a primary key, and I have enforced referential integrity on the join between States in the Regions table and States in the Building Table.

I want to have a user input criteria where if they input "Yes" it appends the North and East data, and if they input "No" it inputs the South and West data.

Any ideas on how to do this?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:41
Joined
Aug 30, 2003
Messages
36,118
Since Yes and No are unrelated to the data, you'll either have to do that manually (dynamic SQL) or add a field to the appropriate table that you can include in the query and add the criteria to it.
 

plog

Banishment Pending
Local time
Today, 04:41
Joined
May 11, 2011
Messages
11,613
Why is this an APPEND query? What's the users ultimate use for this data? WHy must it be added to an existing table?
 

phenley

Registered User.
Local time
Today, 04:41
Joined
Jun 15, 2017
Messages
10
Every quarter, new data is calculated and added into to the table I am trying to append to. The data is in excel and is not the exact same format as the data table, so I import it in from Excel into the building table and then use an Append query to add it to the data table.
 

plog

Banishment Pending
Local time
Today, 04:41
Joined
May 11, 2011
Messages
11,613
So why would users be involved with this process and why wouldn't the entire dataset be appended?
 

phenley

Registered User.
Local time
Today, 04:41
Joined
Jun 15, 2017
Messages
10
I think I may be a little confused by your question.

The user will be given about 200-300 rows of data each quarter in excel. The user then imports that data into the access Building Table, which has the same fields as that in excel. Then, the user runs an append query to add that to the historical dataset. Since there is one field that need to be calculated that is not in the excel data, the append query allows Access to add that into the data that is being appended.

If there's a better process to add the data to the history, I am open to all options.
 

plog

Banishment Pending
Local time
Today, 04:41
Joined
May 11, 2011
Messages
11,613
A database is a container of all data. I don't understand why each user has their own set of data. Why is not all the data going into one main table that every user can access?

Also, you are now talking about adding a calculated value to a table--that too is not how databases are to work. Values that can be calculated from available data should be done so in a query and not stored.

I fear your entire system isn't optimized.
 

phenley

Registered User.
Local time
Today, 04:41
Joined
Jun 15, 2017
Messages
10
I'm pretty sure it's set up the way you're describing. There is one historical dataset table in Access that gets appended every quarter with the new data, which is appended each quarter by one user. All queries to view the data are created from this table.

The data is given in Excel and imported in Access, the calculated value is really just adding a field into the data. There is a field in the dataset called Data Type. So every quarter the user imports the data into the building table into Access (which gets cleared out every time it is used). The append query then appends that data into the historical dataset, but it adds the values "Actual" to the data type.

Then in the same quarter, another query is run to use the last 15 quarters of data to project what the next quarter values will be. Those values are then added into the historical dataset using an append query, but the Data Type field is given a "Projected" value instead.

Does this help clear up the process?
 

Users who are viewing this thread

Top Bottom