Solved

INSERT INTO Statement from one query to another

Posted on 2014-10-24
7
31 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 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