Can I append data to different fields depending on the data within anoth field

[Phoenixenv]

Hi guys, i want to know whether it is possible with an append query to add data to different fields depending on the data in a separate field.

We capture biological (species and environmental site date) electronically (iphone, ipad, android devices).

One of the questions our electronic data forms asks is which trip number is this: T1, T2....because we often revisit sites periodically.

Therefore we have multiple date fields ([DATE1], [DATE2]...) for a site.

I connect the device data capture software directly to an access table, from which i then select and format data using a select query, then run an append query to add that data to the respective tables.

So i want to append the date field coming from the device to the date field corresponding to the trip#, e.g. [Trip1] = [DATE], [Trip2] = [DATE2] etc.

The only other way round this would be to have multiple Date fields within the device forms - not ideal.



So can i use an IIF statement so that the 'append to' field in the destination table is chosen, based on the data in the [TRIP] field, e.g. IIF([TRIP]="T1", [DATE], IIF([TRIP]="T2", [DATE2], etc

Is this possible?

[ranman256]

run 2 queries
qaApd1 to add all records if TRIP = T1
qaApd2 to add all records if TRIP = T2

[NoellaG]

Hi, it's not a good idea to have several fields in a table for the same data. I advise to redesign and normalize your database (http://databases.about.com/od/specif...malization.htm) . Once achieved normal form, appending data will be much easier.

[Phoenixenv]

run 2 queries
qaApd1 to add all records if TRIP = T1
qaApd2 to add all records if TRIP = T2

So you are suggesting i limit the select query using the criteria "T1", then append any results to the field [DATE] in the desired table.

Then effectively run the identical query, but using the criteria "T2", with a subsequent append query, pushing the T2 dates to the [DATE2] field.

That will indeed work. Thanks.