Lookup Columns In Access via Linked Data?
-
Nothing to be sorry about - everyone has to start somewhere!! For a report, you can create a query that has the data as you want it (ie: link the two tables together in the query, and instead of showing the ID field, show the related field that has the text description). This has the benefit that if you export the query to Excel the data will still be formatted correctly. On a form (this works for a report also, but you don't get the benefit with exporting that I referred to), you would have a query that returns the raw data for the form, then a combo box (with its own query to get the data from the related table). Set the data source for the combo to the related query, and bind the combo box to the id field in the main table. Hope this points you in the right direction!
Silence is golden... but duct tape is silver!! Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
I figured it was an Access "cheat" rather than some sort of standard so to speak. But it didn't hurt to ask. I will just make sure he is aware of the fact that he will need to modify the reports a bit to show the data that he wants. Well, I guess a query, as you suggested, would come into play there as well, correct? I mean, create a report that pulls its data from the query, which displays the data as he would like to see it? The only people who have direct access to the raw data (tables and the like) are my boss and myself. He created the original Access database which I am transferring over to SQL Server. The rest of the employees will use the application I am writing. Sorry, I'm quite new to the more intermediate-advanced database techniques and such. :)
Good for you Matt, you have a learning curve in front of you but it will definitely be worth it. When you have the database and app working it will be a natural step to move the report(s) to a proper platform (SSRS or Active etc, NOT Crystal Reports).
Never underestimate the power of human stupidity RAH
-
I am currently writing an application which connects to a Microsoft Access 2007 database. After doing a bit of research, I have decided (and I have been given the go-ahead) to work on transferring everything over to an installation of Microsoft SQL Server 2008 Express. I am successfully able to import the existing data into SQL Server via SQL Server Management Studio Express. I set up all the table relationships and so forth. My question is in regards to the relationships. There is one main table, which is the basis of the application's data. The fields in that table, Repairs, include relationships to other tables (e.g. [Failure Codes]). In Access, the linked fields are setup as Lookup columns, pulling data from the appropriate tables. The fields display the [Failure Description] field (text) in the Repairs table, but of course the field's value is the [ID] (Primary Key) from the [Failure Codes] table. Is there any way to do the same in SQL Server? This is not the most important issue. It is simply a curiosity. The main thing is that my boss is most comfortable with Access. He has never really worked with SQL Server. I told him how we can create an Access database on his computer which will link to all the appropriate SQL Server data (I tested it, it works perfectly). However, when he views the Repairs table he is seeing the [Failure Codes].[ID] value instead of how it was before, displaying the [Failure Codes].[Failure Description] field text while storing its value as the [ID]. Is there a way to change this? He often runs queries to find records matching criteria such as a date or date range. And when he does, Access pulls the [ID] instead of the description text. The main reason I ask is for the fact that he generates reports periodically. I know he uses the Report Wizard in Access. However, when doing so, the report displays the [Failure Code] column's values for each record as the [ID] rather than the [Failure Description]. Now, I know how to add more fields and such to the report, as I am sure he does as well. But are we missing something? Or is it best to put the fields in manually, in order for them to look up the appropriate values?
Now that you have the data in SQL Server you need to create the Views (these are something like queries). In the view you have the main table and add in all the tables that have foreign key (lookup) connections to it. If you use the view designer SQL will create the link for you. It is important that when creating a view the view has the same number of records as the transaction table, this tells you that your joins are correct. Then just add in the description fields you want the user to see. The access app should use the views instead of the tables to see the data.
Never underestimate the power of human stupidity RAH
-
Now that you have the data in SQL Server you need to create the Views (these are something like queries). In the view you have the main table and add in all the tables that have foreign key (lookup) connections to it. If you use the view designer SQL will create the link for you. It is important that when creating a view the view has the same number of records as the transaction table, this tells you that your joins are correct. Then just add in the description fields you want the user to see. The access app should use the views instead of the tables to see the data.
Never underestimate the power of human stupidity RAH
Thanks. I had partially figured that out on my own, before seeing your message. But now I'm even more on the right track. I received the approval to migrate to SQL Server. So this afternoon I installed an instance on one of our server machines at work (SQL Server 2008 Express). And I can manage it from my dev. machine via SQL Server Management Studio Express. I setup a few views and played with them in Access prior to that. And what you just told me will help when I go back to it. BTW, I now know why I was told to get away from Access. Displaying a Windows Form using data from a query could take 5-15sec., maybe even 20sec. Since I migrated to SQL Server, the same operations are practically instantaneous. :-P About the reports. I actually created my own report generation (for weekly and monthly reports). My boss basically said he doesn't require the ability to design the reports himself, but rather he simply needs specific data in preset designs. The weekly and monthly reports, as far as I know, are all he uses. In doing so, I also learned quite a bit about LINQ To Objects today. :) I've been interested in learning more about it, though I hadn't taken the time to until today. It made manipulation of the report data so much easier and so much quicker. Then, as requested, I designed the reports in PDF format using iTextSharp. :) Is there any general advice you have for using SQL Server? I mean, any tips to maintain good performance, security, etc.?
-
Thanks. I had partially figured that out on my own, before seeing your message. But now I'm even more on the right track. I received the approval to migrate to SQL Server. So this afternoon I installed an instance on one of our server machines at work (SQL Server 2008 Express). And I can manage it from my dev. machine via SQL Server Management Studio Express. I setup a few views and played with them in Access prior to that. And what you just told me will help when I go back to it. BTW, I now know why I was told to get away from Access. Displaying a Windows Form using data from a query could take 5-15sec., maybe even 20sec. Since I migrated to SQL Server, the same operations are practically instantaneous. :-P About the reports. I actually created my own report generation (for weekly and monthly reports). My boss basically said he doesn't require the ability to design the reports himself, but rather he simply needs specific data in preset designs. The weekly and monthly reports, as far as I know, are all he uses. In doing so, I also learned quite a bit about LINQ To Objects today. :) I've been interested in learning more about it, though I hadn't taken the time to until today. It made manipulation of the report data so much easier and so much quicker. Then, as requested, I designed the reports in PDF format using iTextSharp. :) Is there any general advice you have for using SQL Server? I mean, any tips to maintain good performance, security, etc.?
Matt U. wrote:
I mean, any tips to maintain good performance, security, etc
Well there goes a book or 2 :laugh: To be honest your data requirements are probably so low that I doubt performance will be come an issue. It is more important to get your structure correct, only store data once being the basic rule. SQLServerCentral.com is an excellent resource for articles and tips, I'm not sure how good their basic info is. I would concentrate on your presentation layer and reports. You'll get the biggest bang for your buck there. User really don't give a rats about the database but will argue over the width and colour of a textbox.
Matt U. wrote:
iTextSharp
This is only a PDF generator, not a report generator. You will at some time need to look into proper reports, I use SQL Reporting Services, the option to embed a report in your app without haveing to use the Server component is excellent for small apps.
Never underestimate the power of human stupidity RAH
-
Matt U. wrote:
I mean, any tips to maintain good performance, security, etc
Well there goes a book or 2 :laugh: To be honest your data requirements are probably so low that I doubt performance will be come an issue. It is more important to get your structure correct, only store data once being the basic rule. SQLServerCentral.com is an excellent resource for articles and tips, I'm not sure how good their basic info is. I would concentrate on your presentation layer and reports. You'll get the biggest bang for your buck there. User really don't give a rats about the database but will argue over the width and colour of a textbox.
Matt U. wrote:
iTextSharp
This is only a PDF generator, not a report generator. You will at some time need to look into proper reports, I use SQL Reporting Services, the option to embed a report in your app without haveing to use the Server component is excellent for small apps.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
User really don't give a rats about the database but will argue over the width and colour of a textbox.
LoL. I wouldn't be surprised if I received some sort of similar feedback. There are quite a few weirdos where I work. xD
Mycroft Holmes wrote:
This is only a PDF generator, not a report generator.
I am aware of that fact. What I mean is I create reports based on data, using iTextSharp to lay the data out in a PDF. The reports are basic. For each new "Model" in a list (of records from the current week or month), it pulls each record for that model. Certain fields are then retrieved and laid out in a table in the PDF file. At the end of each model there is a "Summary" section, which simply sums that model's data up, total handled, total repaired, etc. It works just fine. Does it sound like I should still use SQL Reporting Services? And if so, do you have any good resources for it? Before I created them myself and placing them into PDF I had researched reporting a bit. I couldn't really find any good material that taught the subject well enough for a "beginner". And I didn't find much useful on SQL Reporting Services specifically, as I had seen it mentioned in numerous places. :) EDIT---------- Well, I don't know what I was thinking before. Maybe I did not search correctly. I just found numerous tutorials on SQL Reporting Services. Haha. Thanks a lot, I will read into it now.
-
Mycroft Holmes wrote:
User really don't give a rats about the database but will argue over the width and colour of a textbox.
LoL. I wouldn't be surprised if I received some sort of similar feedback. There are quite a few weirdos where I work. xD
Mycroft Holmes wrote:
This is only a PDF generator, not a report generator.
I am aware of that fact. What I mean is I create reports based on data, using iTextSharp to lay the data out in a PDF. The reports are basic. For each new "Model" in a list (of records from the current week or month), it pulls each record for that model. Certain fields are then retrieved and laid out in a table in the PDF file. At the end of each model there is a "Summary" section, which simply sums that model's data up, total handled, total repaired, etc. It works just fine. Does it sound like I should still use SQL Reporting Services? And if so, do you have any good resources for it? Before I created them myself and placing them into PDF I had researched reporting a bit. I couldn't really find any good material that taught the subject well enough for a "beginner". And I didn't find much useful on SQL Reporting Services specifically, as I had seen it mentioned in numerous places. :) EDIT---------- Well, I don't know what I was thinking before. Maybe I did not search correctly. I just found numerous tutorials on SQL Reporting Services. Haha. Thanks a lot, I will read into it now.
Make sure you don't get into the Server side of it, you don't need it yet, concentrate on "local" reports or RDLC these are the embedded version (same report just delivered without the server). SSRS exports in PDF format, among others.
Never underestimate the power of human stupidity RAH
-
Make sure you don't get into the Server side of it, you don't need it yet, concentrate on "local" reports or RDLC these are the embedded version (same report just delivered without the server). SSRS exports in PDF format, among others.
Never underestimate the power of human stupidity RAH
What tools will I need in order to create SQL Server reports? I can't seem to find that information. I saw "Report" in VS2010's "Add New Item..." dialog box, with the "RDL" extension (or whatever it is; I know I saw it, just can't remember it). The main thing I could not figure out was how to populate the report. I saw the report design view and such. But I can't figure out how to add data controls and bind them to data. My application does not use a generated DataSet. I have a connection class which handles all the retrieval of data from the server.
-
What tools will I need in order to create SQL Server reports? I can't seem to find that information. I saw "Report" in VS2010's "Add New Item..." dialog box, with the "RDL" extension (or whatever it is; I know I saw it, just can't remember it). The main thing I could not figure out was how to populate the report. I saw the report design view and such. But I can't figure out how to add data controls and bind them to data. My application does not use a generated DataSet. I have a connection class which handles all the retrieval of data from the server.
Reports is a fairly steep learning curve, after adding the report you then add a data source and design against that. I always create a stored procedure that service the data requirements of the report. This is then usd as the data source. When you have completed the design you cahnge the extension to RDLC and supply the data from your application by getting the data from the stored proc and passing it and the rdlc to the report viewer.
Never underestimate the power of human stupidity RAH
-
Reports is a fairly steep learning curve, after adding the report you then add a data source and design against that. I always create a stored procedure that service the data requirements of the report. This is then usd as the data source. When you have completed the design you cahnge the extension to RDLC and supply the data from your application by getting the data from the stored proc and passing it and the rdlc to the report viewer.
Never underestimate the power of human stupidity RAH