Question new user needing advice (1 Viewer)

[wandana]

Hi All,

I have experience of OOD and OOP and training by ORACLE in SQL, RDBD and Java, but not a lot of experience in them.

I am now semi retired.

I am doing a small database for a medical app and MS ACESS should be more than adequate

I have 9 tables 1 many to many, 1 one to one, the rest are one to many.

I have written most of the queries and they work, incluing some multi nested sub queries.

My big problem is coding these SQL snippets into VBA so I can create a user friendly interface via forms or reports.

I have recently discovered how to use the query designer, ( I have been hard coding in SQL), trouble is I do NOT understand the code it makes even though it works.

I have accidently overwritten one of my best bits of code, I will try to re-write it so someone may be able to show me how to make it the procedure behind a Form Button

Best wishes, I'll be back

Jim

 

[wandana]

OK,

I just rewrote the bit of code I lost

SELECT surgery.surgeryname
FROM surgery
WHERE surgery.suid IN
( SELECT cmsu.suid
FROM cmsu
WHERE cmsu.cmid IN
(SELECT communitymatron.cmid
FROM communitymatron
WHERE communitymatron.matronname=name));

This snippet wrks fine in SQL, I need that along with many others behind the buttons on some sort of form so users can just call up what they need from their appropriate schema.

I can send dummy data of course, but I think this should be s simple puzzle with a simple solution, its just my ignorance that's in the way!

Best wishes,
Jim

 

[Bryan]

I'm not 100% sure what you need help with, but I'll take a shot.

Your query is working correctly . . . you just need to know how to make it so users can retrieve data using it???

The best way to return data to a user is using a report. Basically, the query returns the data in "raw" form. The report formats the data to make it more readable.

You just need to design a report using your query at its record source. The user can launch the report from a button. You have several options here. You can:

1. Design a form with command buttons that run the report
2. Use the Switchboard manager to design a menu with buttons that launch the report
3. (For Access 2010) Use a Navigation Form with command buttons that run the report.

 

[jdraw]

Welcome. Some of us are fully retired. The OOP and OOD may help you with design and concepts, but may not be directly applicable to Access.
What version of Access do you have? I have 2003 and no experience with 2007 or 2010

Do you have a data model? Can you show a jpg of your tables and relationships?

What in plain English is
-the application you are building?
-a specific issue with SQL and vba ?

There is a series of free video tutorials at
http://www.datapigtechnologies.com/AccessMain.htm

 

[wandana]

Hi JDraw,
Correct on all counts! the OOP/D does help and the Oracle training plus lots of other stuff enables me to have a GOOD understanding of conceps and some execution. I was a programmer in Assembler in an earlier life.

OK I have managed to create a jpeg of my model done in Visio but not sure how to attach it to a message on the forum. having trouble with the process! sorry!

The application relates GP's to surgerys , the surgerys are "aligned" to matrons, district nurse teams, managers and Macmillan nurses, these are fairly dynamic and it is important to maintain a reliable contact lines via phone numbers between the members of the teams.

This is ALL theoretical at present so no reaal data exists but the plan is to test the idea at this stage.

I hope that was OK for plain English... YES the problem is focused right now on converting my working SQL code into VBA in a form that will enable me to create Forms or Reports that are user friendly. e.g. the user has a button on a form saying

SURGERY from District Nurse,

They dlick the button, and a box appears where they enter a District nurse name.

Up comes a list of surgeries with phone numbers that the afore mentioned nurse is aligned to.

I will now go back and see if I can send a JPG of my model!

Thanks for your interest

Best wishes,
Jim

( Its getting late here!)

 

[jdraw]

With Visio, you may have an option to create a jpg or pdf (I forget). I don't have anything that can read the vsd (Visio) directly.
There is a data model here (the only one I found involving doctors) but it may have some value to you.
http://www.databaseanswers.org/data_models/doctors_practice/index.htm

Here are some of the related business facts for that model.
http://www.databaseanswers.org/data_models/doctors_practice/facts.htm

Many years ago I worked in 360 assembler and Macro-11 (PDP). Later with ADABAS, Oracle ...

If you have your Tables in Access and you have used the Relationships window, you could take a screen capture of that.

 

[wandana]

OK,

VISIO does allow a save as jpeg but then it is not recognised by any other software.

One point, ALL my MS software (apart from OS), is 2003, I had a legel version for home use from work and MS still support it and it does most of what I need, so we are both at least on the same ACCESS!
I think I just uploaded a file "District nurse alignment .doc" but where it went I don't know!!

I will look at the relationship option in access

My first assembler code was for emmbeded stuff in a lung function monitor, I loved it, we had to write ALL the drivers for the printers and math etc even a 3 dec place alogytm to get base e logarithms. You knew what every byte did, not like this C++ stuff; I am sure that was written as a rag week stunt

I will try to create a pretend dummy front end for my project so you can see what I am hoping for.

 

[wandana]

Hi, Jdraw,
Below is a JSP of a screendump, I could not find a way to export straight to a file that could be attached to a message on the forum.

I sent a working sql query in an earlier post.

I wish to enable the user to invoke that query by pressing a button a form that will result in the output being displayed on a form or report.

I also need t to implement a query that requires an input,, such as district nurse from GP.

I have NO trouble with the query in SQL, it is the form design and VBA that I am struggling with.

using ACCESS 2003


Thanks and best wishes, Jim

 

[jdraw]

Is your database confidential (contains personal info etc?)?
Can you "dumb it down" remove personal info,use Joe Blow etc for names?
And post or place a copy for download somewhere?

The table names TL DN ... are not too informative.

 

[wandana]

You are right, table names like dn and TL, ( District nurse and Team leader) are meaningless but I use the shortcuts to save time typing as I spend most of my time re-doing the code to try and make it work.

And for that to happen, I need someone to just show me how to put the following SQL code into a button or something on a form or report so a user simple selects the right button to get a printable report.

Everyone is being helpful and am very grateful but no one has answered that question, which was in my first post.

All the data in my table is ficticious but I think that the person who can help me will just post an example of my code in a button on a form. I have writen some VERY simple code but cannot embed that SQL!

SELECT surgery.surgeryname
FROM surgery
WHERE surgery.suid IN
( SELECT cmsu.suid
FROM cmsu
WHERE cmsu.cmid IN
(SELECT communitymatron.cmid
FROM communitymatron
WHERE communitymatron.matronname=name));

Best wishes,
Jim

 

[jdraw]

Generally, with a select query(your code) with a parameter(name), I would create a parameter query.

On a form, you might have a combo box (dropdown) containing a list of matronNames,;
and a button to run the Query.

Once the user chooses a name from the combo, they click the button and your query with the appropriate parameter value would be executed.

For a button on a form, on the button click event, you would run the code to assign the chosen name to the query, then run the query.

NOTE: Since I don't have your tables and data, I can not offer a more specific example.


Also, NAME is a reserved word, so names for fields and variables is important.

see http://allenbrowne.com/AppIssueBadWord.html

 

[wandana]

Thanks Jdraw,

I have attched, ( I HOPE ), the three files associated with this query and the data is all fictitious!


I hope you understand what I need, I need an example of how to enbed my SQL code in a form or report so that it will create a reult from those tables.


It is possible that when I started this thread I should have been more specific about my problem, I am always going to folk about how important the requirement spec is and I have failed to make it clear in this instance.


I may start a new better specified thread, but I am sure that you have the kills to answer this one. I look forward to hearing your next contribution.


We are preparing here for the Olympic Games, we am about 50 miles away and will watch a bit on Tele, My wife will be going to watch some of the equestrian stuff, we used to ride a lot in Australia, but I would not enjoy the walking or the crowds. I hope it goes well for everyone, and that includes EVERYONE, I don't care who wins as long as they have a good time and nowone is hurt.


Best wishes,


Jim