Who knows Access? 1 Database - 2 Problems

Live forum: http://forum.freeipodguide.com/viewtopic.php?t=69463

TryinToGetPaid

17-09-2007 07:37:42

If you do -- I need some help with something. This isn't something that I NEED done - but it would be cool if I could do it.

Right now I have 2 Tables, both from Excel. And 2 reports. The reports pull information off of the tables. When you open the report it asks what office you want to audit, you type in whatever office and it finds that same ID on the spreadsheet and then matches the ID number to pull demographic info and the people located within that office.

1st thing - I would like to do is combine my 2 reports to one. One is just a coversheet for the other, but I can not figure out how to get them to only enter the office name once and have everything blow in on one report, but be two separate pages.

[bedf636cd70]MORE IMPORTANT[/bedf636cd70] 2nd thing - I would like to instead of them getting ASKED what office they wish to audit, I would rather have a list populate where they can just pick which one they which to audit and everything would blow in automatically but I am yet to figure out that.

If anyone can help me much karma and a few dollars is yours, and yours alone!

bballp6699

17-09-2007 10:18:42

I'm sure you could easily write something in VBA that does that. I'm not 100% sure what you're trying to do as I thought your description was a little confusing, but you can definitely populate a drop down box relating to table data in VBA.

TryinToGetPaid

17-09-2007 11:09:31

I can not write anything in VBA -- thus the problem -)

And yes I just need a menu that when you click to open the report you pick which office you are working with.

tylerc

17-09-2007 13:50:25

I'm in a class right now at IU that's required for all business majors called K201 The Computer in Business. Right now we are learning Access. It sucks. Not extremely hard, just a lot of work.

manOFice

17-09-2007 13:56:39

[quote034c1775dd="tylerc"]I'm in a class right now at IU that's required for all business majors called K201 The Computer in Business. Right now we are learning Access. It sucks. Not extremely hard, just a lot of work.[/quote034c1775dd]

Access can be tricky but so powerful. I had to take this database class in college... all this sql and access stuff. It was crazy hard but so cool when it worked, lol

bballp6699

17-09-2007 14:04:55

I thought SQL was pretty simple. shrug

manOFice

17-09-2007 14:06:59

[quote5c5230fb32="bballp6699"]I thought SQL was pretty simple. shrug[/quote5c5230fb32]

SQL is pretty simple, it can get tricky but it makes sense as you're doing it so it's not really that hard.

CollidgeGraduit

17-09-2007 14:15:57

[quoteff1dca197e="bballp6699"]I thought SQL was pretty simple. shrug[/quoteff1dca197e]

The language itself is pretty simple.. but once your query starts getting big, it can get confusing.

I've done some monstrous, nasty queries in the past few months. It's enough to fry my brain by the end of the day sometimes.

dmorris68

17-09-2007 14:44:43

Access. lishudderli

For simple desktop use and MS Office integration, it's... okay... but I've seen and/or had to support dozens of horrible multi-user database applications made with it, or using the underlying JET database "engine." Access databases are not meant for remote, multi-user access, and since invariably that's what happens, I avoid it like the plague.

To answer the original question, I'm not sure I understand fully the problem either. The mention tables being "from Excel." Are you talking about Access tables being loaded from an Excel worksheet, or are you talking about hitting Excel worksheets directly from Access through the Excel ODBC/ADO driver?

You should be able to mostly point & click your way to simply reports and forms in Access without writing much if any VBA script. Fortunately (for me, not for you) I no longer have Access on any of my machines -- nor would I allow it -- so I can't really walk you through it.

And yes, SQL is a simple language. However as CG pointed out, writing complex queries in an optimized fashion is both science and art, as is efficient database design. There's a reason why DBA's are paid well.

TryinToGetPaid

17-09-2007 15:03:20

They are imported from Excel sheets -- they are loaded as Tables in Access.

LovelyLady

20-09-2007 11:36:50

How are the users accessing the reports? It sounds like you need to add a combo box (drop down box) control somewhere and attach it to a query. I might be able to help if I had more information. )

I would imagine that people are typing all sorts of wrong information and other junk where they should be entering the office name. lol. I've been there. Anyway, let me know if you still need help.

TryinToGetPaid

20-09-2007 11:56:46

Exactly -- one mistyped word and you get errors.

They go into the database and open one of two reports (One is a cover sheet the other is the actual Audit report itself) Once they open they are prompted "Which office do you wish to audit?" with a text box for entering them in.

What I would like is when they open either report it still asks them what office they would like to audit but have a list of those offices that they can choose, instead of manually typing it in.

LovelyLady

20-09-2007 14:23:11

Ok, so can you create a table with the names of the offices and set up a query to feed the names into a dropdown control? Are you able to modify the form from which they are selecting the reports? If so, once you set up the query, you can add a combo box control to list the offices and set up a command button to activate the query. I've handled things this way in the past.

Also, I've found that the Knowledge Base and developer's forum on MSDN are very helpful when I get stuck on something. Am I allowed to post a link to it? If not, you can probably just Google it.

doylnea

20-09-2007 14:55:44

[quote55a60ce0d3="LovelyLady"]Also, I've found that the Knowledge Base and developer's forum on MSDN is very helpful when I get stuck on something. Am I allowed to post a link to it? If not, you can probably just Google it.[/quote55a60ce0d3]

yes, please post a link - there are no rules forbidding linking, unless you proft from the website that is linked to.

TryinToGetPaid

20-09-2007 15:22:13

I am not to sure how to do what you mean, I am total newb to Access and getting what I got took me over 8 hours of fiddling.

LovelyLady

21-09-2007 07:38:52

Ok. Here are some links that might help. I've used the MSDN newsgroups quite a bit in the past. I'm not sure if you have to be subscribed to post a question, but you can search the various Access forums and get an answer to almost anything. You can also find samples there and on the UK forum link below.

I might be able to pull some sample code from databases I've created. If you can bare with me for a day or so, I should be able to send it to you. If you don't hear from me, please PM me, cuz that probably means I forgot...lol.

Hope these help

Access Forum - UK [=http//www.access-programmers.co.uk/forums/] Access Forum - UK

Access Forms [=http//msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.public.access.queries] Access Forms - You can also access other newsgroups (Access Reports, Access Queries, etc.) by clicking on the dropdown box next to the search box.