Solved

INSERT INTO Statement from one query to another

Posted on 2014-10-24
7
28 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now