Query and report building?

rkuk

New member
Local time
Today, 23:43
Joined
Dec 18, 2009
Messages
6
hello all,

I'm totally new here. This site looks like a great place to learn some new access skills. I have used access to a basic level in the past, and I'm hopeful my request is possible!

Basically I am compiling a bunch of comprehensive statements. I would like the to create a list of statements (which I have done in MS Word), and then assign a number to each statement in access. That's the first step.

The next bit I would like to do is create specific reports from the use of the values (I hope I'm explainign myself here). An example could be:

Action
1. The quick brown fox jumps over a lazy dog
2. The brown fox jumps over big dog
3. Lazy dog was sat on the floor whilst the super fast fox leaped over him.

Setting
4. The weather outside was cold and miserable.
5. The weather outside is sunny and warm.
6. It's lovely and cosey indoors.

Genre
7. The genre must be comedy.
8. The genre must be horror.
9. The genre must be sci fi.

Ok - let's pretend they are my options (my text if you like). As you can see each statement has a number assigned to it at the front.

I would LOVE access to be able to process a combination I give it and then produce a report with all the text that I specify. I can then copy this back into MS Word and format it - no problems there. It would just save me a HECK of time and prevent me from making clumsy errors (as I've already experienced bewteen copy and pasting over multiple areas of documents :eek:)

e.g. I select statements: 2, 4, 7. And the outcome would be:

The brown fox jumps over big dog
The weather outside was cold and miserable
The genre must be comedy

(I'd like the paragraphs to be seperated when the report is formed).

Surely this is possible somewhere? I wasn't quite sure what terminology to use when I searched for help, and then I stumbled across this jolly place.

REALLY hope someone can help / point me towards a good tutorial.

Thanks guys
RK
 
OK, I am taking it your three items are fields. Action, Setting, and Genre. Put those three in a query. Simple way to show you what it is doing is in the criteria of the fields put "Pick Action", "pick Setting", and "Pick Genre" when you run the query a parameter box will pop up... in your example pick 2 then 4 then 7. this will return your desired results. This query will be the source for your report.
On the report you can simply put each text box on a separate line.
If you are looking to copy and paste you probably want something a little different..... You will need to concatenate the three fields into one. Stick an unbound memo box in the report control source as =Action & Chr(13) & Chr(10) & Setting & Chr(13) & Chr(10) & Genre ...... You can also do this in the query.... add another field to the query say... Paragraph:Action & Chr(13) & Chr(10) & Setting & Chr(13) & Chr(10) & Genre ... In this case your control source for the memo box on the report would be "Paragraph"

I think that should work.........
 
Hi Curtis and thanks very much for the reply...you may live to regret helping me here :p. As unfortunately we need to roll things right back due to me being an absolute beginner.

Do I just need to create 1 table. Within that table do I then insert my statements (I've been inserting them as memo's as that allows longer than 255 characters)

Or...would I need 3 different tables with each of my different kinds of statements? (surely that would change the number of the statement I need to pick?) e.g. 1,2,3 then 1,2,3 again and so on?

Basically I just want to figure out some sort of command that will automatically select specific statements and then I can compile a report. The context I am using it in is to create student reports. So I have a lot of differing statements, and I want to pick custom combinations. Then I want the computer to write the report for me and I can copy and paste it then customise and tweak the necessary bits.

I have the statements prepared, I just need to know what to do next.

Apologies for being so useless! lol
RK
 
Last edited:
yes Curtis!! :D :D :D

that does it!!! The only thing is I don't know how you did it! lol. From what I can see:

You created 4 tables:
tableaction
tablegenre
tablesetting

then one which you have used to generate the report scenario.

I thought I would need a few tables, rather than just fields - so that answers that bit. :) Though I don't know what the the "+" sign is for / how it's been created before the Id column.


I don't know how you made the query - was that easy?

Then I'd imagine you created the report from the query?

You can see how much of a novice I am.

Though basically you have produced what I want. I guess a column for the students' name would be handy (that could be a common value on each table) If we did that, would it be possible to call all the values JUST for one learner? Then I could copy the report into their word file, then run another report for the next learner.

Someone else suggested me using macros and saving statements to a hey in MS Word, but I'd much rather have a bash at this as if I change a statement it is instantly updated.

Is there any way on earth you could explain to me how you made this table for beginners? It would be such a great help!!!!

Thanks ever so much for your time!!!!
RK
 
Well, thats a lot of explaining :)
You can look at the relationships, that will show you how the tables are joined or related. Also you can open the query and put it in design view. That will show you how it is setup. try using the wizards to create queries, tables, reports... That will also help. And No, students name would not be a common value in each table. I would either have a table for student info, or if all you need is a name you could put a field in the tblStatement. (But not an advisable way to do it) Read up on "normalization"
My best advise would be read, read, read.... Lots of books around and just google "MS Access tutorials" a lot of info on the web including some tutorials.
As for using macros to save statements to Word... I don't see the need. Your statements are already here.
I did your sample really quickly, so it CAN be done a little better. But it gives you an idea and a starting point.
 
Thanks again Curtis.

I have looked at your example and I have managed to replicate the setup (to an extent). I have linked the tables. When I type in a number (as I desired), it reveals the statement :), though I can not het the compiled 'formula' to quite work.

I have a few issues including - the autonumber has gone from 1 (in the first field) to a massive number in the next (not very auto in my novice opinion! lol). This has happened on a couple of the tables. I looked at how to reset this, but it looks quite complex. Would you be willing to take a look at my setup if I emailed it to you? I totally understand if you don't want to / don't have the time, though you'd really be helping me out. I really want to make use of this method of reporting as it will save me a lot of time in the future but I am no programmer by any means.

Cheers
Rk
 
Sure, but you can go ahead and post it here if you like.
As far as the auto number goes, it will start at one... add every time you add a new record to that table. If you start with one, add 20 records, then delete those 20 records your second record will have a number of 22... It does not start over simply because you deleted a record. I used the auto numbers in fields that are primary key fields. You do not have to use an auto number, you could just use a number, but every one must be a different number.
As for the formula.... I'm not a great programmer myself! :) But it's probably just a syntax error. If I don't see the error there are a lot of really good programmers here on the forum.
 
Hey Curtis,

Thanks very much. I worked pretty much all day to try and figure this thing out. I got pretty close to your example! The thing I can't get to work is the compile action. It just won't work for me - obviously somethign I'm not doing right here.

I also can't get the report to be the layout I want - like your's. I keep getting my headings, and the whole point is to generate the report I want and then copy and paste the text with minimal formating in MS Word, but unfortunately all the reports I tried, would leave the text difficult to copy (as sometimes it was not all viewable in the report), and when I did get it into MS Word the justification was all over the place - I could have copied an pasted all the records in the custom combinations by hand in that time.

I'm still proud of what I have achieved (no laughing to all you experts there! lol). It's all very new to me, so I think I did ok linking some databases. If you don't mind having a look at the setup when you get a chance that would be great. You might be able to fix the compile action for me too in my query (it's not there at the moment). All the fields in the query are in the order I want. In the tables section / pane the tblSTATEMENT is the main link for the relationships (well you'll see that anyway).

I welcome any feedback. Thanks again, as searching for my direct needs on the internet has been difficult to define. If anyone has a direct step by step tutorial on how to compile such reports from scratch please do post a link for me. Or if anyone is tempted to write one I'm sure there would be others out there like me grateful the help.

Cheers
RK
 

Attachments

OK, I added the concatenated field to the query. It's not too difficult... First I called it "compiled" probably a bad choice of words, but... As you see to build this you click on an open field area in the query grid... Type your field name "Compiled" followed by a colon (:) Then all it is is each of your field names [name] plus a little code "& Chr(13) & C, & " with out the quotes...then [nextfield]... and so on....
I just simply put this field into the report.
What you need to do is build a form. Forms are used to enter data into tables. You really shouldn't enter data directly into a table or query.
About using "Word" not sure... My first question would be... Why do you need to transfer it to Word?
 

Attachments

Hey Curtis,

Sorry for the long time of a response - started typing a reply a while back and ended up getting side tracked / went out etc! lol. Anyway, just to say a massive thanks for looking at my db for me and reconstructing my query to do the compiling.

I think you could be right and I may need to look into using forms...however I go about that! lol.

Thanks again for the help and all the best for 2010. I'm sure I'll be back around here for more help and support as I venture along with databases! hehe :)
 

Users who are viewing this thread

Back
Top Bottom