Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 37
  • Last Modified:

INSERT INTO Statement from one query to another

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
lynksko
Asked:
lynksko
  • 3
  • 3
1 Solution
 
Neil RussellTechnical Development LeadCommented:
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
 
lynkskoAuthor Commented:
"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
 
Neil RussellTechnical Development LeadCommented:
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
Industry Leaders: 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!

 
lynkskoAuthor Commented:
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
 
Neil RussellTechnical Development LeadCommented:
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
 
lynkskoAuthor Commented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now