Solved

INSERT INTO Statement from one query to another

Posted on 2014-10-24
7
33 Views
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,

Lynks
0
Comment
Question by:lynksko
[X]
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
7 Comments
 
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.
0
 

Author Comment

by:lynksko
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.

Lynks
0
 
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:lynksko
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.

Lynks
0
 
LVL 37

Accepted Solution

by:
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.
http://msdn.microsoft.com/en-us/library/office/ff973807(v=office.14).aspx
0
 

Author Comment

by:lynksko
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.

Lynks
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

752 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