Table Setup

[aaronjlaw]

Hello guys, I am new as you may guess and I have not used Access for a number of years and I have no idea about how it works in comparison to Oracle which I used for uni projects.

At work I have been tasked with a few Access based problems and one of which is making one of their handwritten feedback forms be input on Access so then responses can be graphed and analysed.

Is it possible to give written answers a number so they can be graphed.

For example.

Question 1 - Give me an example questions?

Answers:

1. Yes
2. No
3. Unsure

So you can see how many have each one when you graph the results. As I have it created with foreign keys at the moment it is displaying the text when I do the queries.



Also... is it possible to make a button so an interactive pdf report can be sent to all the selected user's email addresses?

 

[Pat Hartman]

Welcome aboard
Jet/ACE are relational databases just like Oracle. You will find some differences in the features offered, data types, and SQL syntax but nothing dramatic. If a schema is properly designed for Oracle, it is properly defined for Jet/ACE.

Access is the RAD tool that is often confused with Jet/ACE because it is tightly integrated with either Jet or ACE (depending on the Access version) to hold its own objects. Access as a FE can link to any relational database that supports ODBC connections as a data store.

What you are looking for are combo boxes which are control types. The combo box has a RowSource which lists the options in addition to a ControlSource which stores the chosen option. A RowSource with only a couple of options Female/Male/Other might use a Value List for those three values. A RowSource with more options or options that the user should manage himself (department names) should be kept as a table with it's own maintenance form.

If you examine the properties of a combo, you will see it has the two I mentioned plus, column count and widths and lots of others. So for your example, You could use a ValueList defined as:
1, "Yes", 2, "No", 3, "Unsure"
Once you set the column count to 2, Access will see this as three pairs of columns rather than 6 independent values. The bound column would be column 1, The width of the first column would be 0 and the width of the second will be .5". That will hide the number and show the text but store the number in the table.

 

[aaronjlaw]

Thanks for your reply. So this way would I be able to have a legend on the graph to show which numbers mean which answers so the admin team can understand the figures etc?

I had combo boxes set up but not with the correct columns as you have just explained.

Thanks.

 

[Pat Hartman]

A legend is an option of the graph object. It is drawn from the recordset used to populate the graph and doesn't have anything to do with combos. The query used for the graph would join to the lookup table on the numeric ID field and select the text value you want to display. That text field is what you would use for the legend.

 

[aaronjlaw]

How would I go about doing this on the query menu? Do you have any useful links for this sort of thing?

Many thanks!

 

[aaronjlaw]

I basically need this so I can input data as words (via combo boxes) but then it adds to a total of the same answers in a query so I can then graph it. It still needs each record to be intact though so it can be viewed as a whole. I cannot show you a picture example as I do not have enough posts it seems!

 

[Mihail]

I cannot show you a picture example as I do not have enough posts it seems!

You can upload anything but only in ZIP format for now.

 

[ButtonMoon]

Welcome aboard
Jet/ACE are relational databases just like Oracle. You will find some differences in the features offered, data types, and SQL syntax but nothing dramatic.

I think I nearly fell off my chair when I read that! Possibly what Pat means is this: there is a certain subset of Oracle features that do have equivalent or similar support in the Jet/ACE world and the differences across platfoms in those common features alone are probably not too difficult to get to grips with. It's a fairly small subset of features to be sure but many of those basic features will at least be recognizable to anyone familiar with a SQL DBMS but new to Jet/ACE.

Aaron, I know it's not the main subject of your question but in case it helps, here's a list of Oracle features compared to Microsoft SQL Server:
http://psoug.org/reference/sqlserver.html
most of the features labelled as "no equivalent" in the SQL Server column will also have no equivalent in Jet/ACE.

I think the biggest obstacle for people familiar with other SQL DBMSs is the Access UI itself, which unfortunately obscures many of the standard SQL features and makes it unreasonably hard to do some pretty basic things. You can of course bypass the UI fluff if you want to, it's just that Access doesn't make it all that easy to do so.

 

[aaronjlaw]

I used Oracle at uni to do some very basic things and also used mysql for a website but I have not used the Access UI since I was at school and on version 2003.
I am confusing myself continually but want to use Access as its the best way to do what the admin team want.

Buttons to make graphs, reports and input data. All of which Access can do nicely if I knew what I was doing haha!

 

[Pat Hartman]

Sorry Button. I hope you didn't get hurt. Very few developers ever even get to create tables in an Oracle database let alone use the interface to create queries or manage the database. I didn't mean to say that Jet/ACE were the same as Oracle. I was referring to the part we all use to build queries which is SQL DML. A relational database is a relational database whether it is Jet/ACE or Oracle or DB2 and the SQL variant used by one will be recognizable to anyone familiar with SQL and a schema that is properly normalized for Oracle would be implemented the same way with Jet/ACE with the exception of certain data types which are not supported.