INSERT INTO Statement from one query to another

Posted on 2014-10-24
Last Modified: 2016-07-11
Have a form in Access 2010 that takes data from a price_list_qry, connects to client_INFO_qry and appends to history for Billing.  There is an item in the price list that clients have done either 6 months or one year later and I have a field called ReminderDueDate in Both the Client_INFO_qry and Billing_History_tbl.

My client_INFO_qry takes the date the item was received and calculates 6 months or ONE year from that date called NextVacDate6Mo or NextVacDate1yr.  I currently cut and paste the applicable result into the ReminderDueDate but would like that to happen automatically.

I'm sure there's a simple  INSERT INTO I could write but what I can't figure out is that there's two different times 6Mo or 1yr depending on the Item and what gets really tricky is one item varies depending on age, But I'll deal with that later once I figure this one out.

The db is for a horse vet and the items are vaccines.  Some vaccines are given every six months and others yearly.  For instance, Rabies is yearly.  Rabies Vaccine is in the price list table.  Once I enter the DateOfVisit the NextVacDate calculations appear and then I cut and paste.  My db is not relational.

Thank you,

Question by:lynksko
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 37

Expert Comment

by:Neil Russell
ID: 40402834
is that all fields in the same table? why not just use a calculated field that WORKS OUT the date for you and the job is done.

Author Comment

ID: 40402890
"All fields in the same table"  Do you mean does the Clients_INFO_tbl, Price_List_tbl and Billing_History_tbl have the same fields?  If so, Yes they do.  How and where would I have a calculated field to work out the 6 mo or 1yr date/dates?  Assuming by date you mean ReminderDueDate.

LVL 37

Expert Comment

by:Neil Russell
ID: 40402906
OK, it begs the question, why you feel the need to store the same piece of data in two places.  This is a design flaw from what you describe.  If it is one piece of data then it belongs in one place.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 40403131
Yes, you are correct, it is a flaw.  I'm currently trying to convert my db to be relational to solve that problem.

 As far as your suggestion,  I think what you are saying is to have a  qry column called:  NextVacDue and write that calc to say something like:  

NextVacDUE: If [Item] = "Rabies Vaccine" Then
 [DateOfVisit]+365 Or If [Item] = "Influenza Vaccine" Then [DateOfVisit]+180 etc.

I just don't know how to write the code in the qry.  Also, one vaccine the "West Nile Vaccine" is tricky.  If the horse is 5 yrs and under or 15 yrs or older the next due is six months.  However, if the horse is between 6 yrs and 14 yrs then West Nile is only given once a year.  So it would have to take into account DOB or the field that actually has the birthdate listed in age i.e. 10 years, 9 months.

LVL 37

Accepted Solution

Neil Russell earned 500 total points
ID: 40403183
Ok so your quest is far more complex than first seems.

In Access 2010 Microsoft introduced something called data macro's.  They are akin to Table Triggers in MSSQL/ORACLE/etc..

You can code the whole of your logic into a VBA Macro and then set that as a trigger for the table.

Its not a "Simple" process but well documented.

have a read here on MSDN.

Author Comment

ID: 40403343
I'll give it a look.  Would this problem be easily solved without the age related issue.  I can still do that part manually because there are only  a small amount of unknown ages.  

I attempted the qry calulation:   NEXTVACDUE: If [Item] = "Rabies Vaccine" Then
 [DateOfVisit]+365 Or If [Item] = "Influenza Vaccine" Then [DateOfVisit]+180 but received an invalid Syntex error, missing operand without an operator and think it's just because I don't know the proper way to code it with () [] "" and such.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question