Query to add records?

[DataGirl]

Probably overthinking this. . . seems like it should be simple - but I can't get it!!



Trying update or append queries, thought those would work! (Tried IIF function - worked, but it just creates new column in query. I need a new record in a table.)

So, two tables: Contacts - has a yes/no field. Based on that "true" or yes field - I'd like to update records in my other one-to-many table called History. History has a field called DateRan - I'd like all the contacts with a yes to have a corresponding DateRan record of "March 2017." I'd like to run this every month.

I do also need a query to 'clear' my yes/no field before/after each monthly update, so I can start each month fresh. Figure that's the easy part. Haven't worked on that one yet though! LOL! Probably not the best design. But thought this might be a quick fix.

I have linked tables, Excel sheets to the yes/no field, so re-design is not an option at this point.

Please Help! What I am missing!

Eventually, I'd like to be able run a report/query that lists Contacts, History [DateRan] and my other table Notes.

[Micron]

post what you tried that almost worked? Or write out the intent of the sql using plain language...

[JoeM]

I have linked tables, Excel sheets to the yes/no field, so re-design is not an option at this point.

Are you saying that these Tables in Access that you want to update are actually linked Tables to Excel?
If so, you will not be able to update via Update or Append Queries in Access. You actually cannot update linked Excel data from Access without the use of VBA.
See: https://support.microsoft.com/en-us/...or-access-2002

[DataGirl]

OK - so no linked TABLES - a linked query to Excel.

[DataGirl]

Current simple query has three fields:
Contact tbl - Name
Contact tbl - Current Issue (yes/no field)
History tbl - DateRan

I add this expression -
Expr1: IIf([Current Issue],"March 2017","")

This works - but I need that "March 2017" in the DateRan field. Which is where I started the append query thinking. But can't figure it out. (I'll need to do this every month.)
Once I figure this out, I'll need to replace all the yes in current issue as no or false. Then I'll start my 'yes's' for the month of April.

Again - DateRan is in History table. Set up History table with one to many relationship Contact tbl auto ID ---> ContactID in History table. Where all records from Contacts and those from History are equal.

Not sure I'm explaining it well! Thanks for taking the time!

[DataGirl]

Oh, and I only drive the data 'down' to Excel. It doesn't ever come back 'up' to Access.

[JoeM]

I am still not sure I understand the relationship with Excel.
These two tables, History and Contact, are they "native" Access tables, or linked tables?
If they are "native" Access tables, then I fail to see where Excel comes into this at all.
It would simply be Update Queries involving two Access tables.

[Bulzie]

Sounds like the 2 tables are linked by Contact(ID) = History(ContactID). I would make backup copies of both tables first. So maybe 2 update queries?

Query1: Make it an Update query(tab at top), pull in both tables and link by the ID, select CurentIssue and DateRan fields into colunns. In the criteria box for CurrentIssue put "Yes". In the updateTo box for DataRan put Now() and Run it
Query2: Make it an Update query(tab at top), pull in Contact table, select CurentIssue field into a colunn. In the criteria box for CurrentIssue put "Yes", In the updateTo box for CurrentIssue put No or null and Run it

I also not sure where excel fits into this. Maybe you just export the data to excel at some point?

[DataGirl]

I am still not sure I understand the relationship with Excel.
These two tables, History and Contact, are they "native" Access tables, or linked tables?
If they are "native" Access tables, then I fail to see where Excel comes into this at all.
It would simply be Update Queries involving two Access tables.

So the Excel link is only for my boss - he's used a certain Excel sheet for years. So I have a linked query, set up the link in Excel, so boss-man can see the data like he's used too. I ran two sheets - one that's the query, the other sheet (yet another link!) has linked cells with formatting he likes. Then I just 'refresh data' in Excel.

Probably shouldn't have mentioned the link - really has nothing to do with my current issue. Sorry for the confusion.

I do appreciate your time!

[JoeM]

OK. Then you should be able to do the Update Queries to do what you want. Bulzie laid out some instructions for you.
Have you tried those yet to see if you can get it to work?

[DataGirl]

I will try that!

I should have realized during creation that I would need a history for each contact. This seemed like the best way to add (the month) after the fact. And my Contacts could have 1 month (or many months of history!) I thought a linked table would do the trick.

My concern is that my new table (History) has no records! Hoping the update query - as you described, will add the new record in my new History table (and link to each 'yes' contact so I can query later!)

Thank you for reviewing - I'll work on this later today!!

[DataGirl]

Yep, so I've been able to do this before - it updates - add records to History Table - BUT, when I do a check by a new query with Contact name and date ran - the DateRan field is empty for every contact.
What am I missing - why doesn't it seem like the DateRan field actually links to the Contact table?

[DataGirl]

So I checked the new records in my History table - DateRan field has date, great! My linked field ContactID (to ID auto number field in Contacts) is EMPTY. I'm clueless.
I have one other table in this bitty database called Notes - set-up just like History. Yet it works! LOL!

[DataGirl]

Ah-HA!! Got it. Just needed to step back and look - I needed to update that Empty linked field. So I added my linked (ContactID) field to the query too - and updated to [Contacts.ID], the auto index from my contacts table! Viola!! Now I'll just tweak the "update to" to ask for my monthly label with a parameter box, update. Then reset my yes per Bulzie's directions!
THANK YOU ALL!!

[JoeM]

Note the different kinds of Action Queries:

Update Query: this will update a field in an existing record (it does NOT create new records, only updates existing ones)
Append Query: this will create a new record in a table

Your links sound a little funny to me. Are you sure you are linking on fields that have matching values? Usually, you don't have links between two Autonumber fields. If only one is an Autonumber field, then the ID it is linking to in the other table would have had to been created using that precise Autonumber from the other table.

If you are running into trouble, it might be best to post some data examples of each table, and your expected results. Or better yet, you can upload a copy of your database (just remove any sensitive information first). I can take a look at it later this evening when I am home (cannot download from my current location), or someone else may take a look at it before I get a chance to.