Access Novice Taking On Large Project - Need Guidance
Feb 13, 2008
Greetings Access Geniuses,
I am currently in the midst of conversation over at utteraccess.com about a large database project I am working on.
I will post the most recent updates here, but if you want to download the database for your own review, you'll have to go over to the other forum. By the way, this is NOT an advertisement. I am a real person with a real, significant, and immediate need of as much assistance as possible for this project.
Before I go on, a few things about me. Though I am in the legal profession, my technological background is very strong, including a high level of proficiency with MS Office apps with the exception of Access. I know my way around the program (the result of its homogeneity with the rest of Office), but have not made a real database in the past. I also do not know any programming languages.
I have recently indulged in an Access crash course of sorts, including some book and online study on things like planning, design, and normalization, but I am still having difficulty wrapping my head around making it work.
That said, the link to the other discussion is: [W W W DOT]utteraccess.[ADD DOT COM]/forums/showflat.php?Cat=&Number=1590364&page=0&view=collapsed&sb=5&o=&fpart=all&vc=1
Feel free to gloss over it to see how things have progressed. Below is a paste of my most recent substantive post. Any help anyone can give me is of great value to me and I really, really appreciate it.
_____________________
Hi Everyone,
Attached is the most updated version of my database project for your review. I have also attached a sample of the output we would like to have for each product. This sample is not based on actual data, but it clearly shows what we are trying to achieve via a form of some sort. More on this in a minute.
The following changes have been made to the DB:
-Changed tblTrustProspectusVersion to include the appropriate data, based on our business model.
-Added descriptions to all non-PK fields.
-Created relationships to illustrate how things fit together. Note that these are NOT the actual relationships, but are for illustrative purposes to help everyone here (including myself) further understand how things fit together.
Our Business:
I am part of my company's legal department. Our team handles a number of different things primarily associated with Securities and Exchange Commission (SEC). filings. We have two major product lines. Everything that happens with one in this DB happens with the other as well.
Each product within a product line has certain features and other necessary information we need to see when doing our filings (see the attached sample output). These features and necessities include, but are not limited to, various statuses, various numbers associated with the SEC, various important dates, etc.
In addition, each product has a certain "fund lineup" associated with it. These funds are made up of two components: the name of the fund (aka "portfolio") and the name of the subadvisor to that fund.
Futher, each of these funds is associated with a certain Trust. The SEC requires us to send prospectuses to clients based on these Trusts, which, as I mentioned, are comprised of the said funds.
Basically, we need to be able to select a product from a drop-down list and have all of the aforementioned information populate instantly.
Before I close, one question with respect to my "tblProductFeatures". Like I mentioned, each product has a certain set of features associated with it. Each feature has a certain fee associated with it. These features come in four basic categories: Living Benefits + fees, Death Benefits + fees; Maintenance fees (just short list of the possible fees); and 12b-1 fees (another short list of fees).
Since the features can be so easily broken down, should I add them to their own tables?
Please consider this as you give your advice on how to acheive my desired goal.
I hope this makes sense.
And again, thank you all so very much for your help thus far.
First off sorry for jumping in and asking questions on my first post. I have been searching the net templates and answers and while it has got me so far I still cant find specifics I'm stuck on and would appreciate any help.
I have an access design project to do for uni. To give you some background I've done some VBA programming in excel and have a good grasp of office and windows but haven't really used access before.
I have got Ms Access Inside out (bit over my head) and Ms Access Step by Step and have been working through their tutorials which have helped my understanding of access but dont arnt really specific enough to my project (or maybe i'm missing something)
The Brief is as follows:
"You have been instructed to design and develop and information system to capture students module choices for the business school. The system will run on MS Access and be supported by programs written in visual basic. Spec is as follows:
The system should be designed so that students can automatically select their options for semester 1 and 2 from a screen which will indicate to them whether they have broken any of the following constraints: -> choose 30 credits in both first and second semester -> business 2 cannot be selected unless Business 1 is -> Programming 2 cannot be selected unless programming 1 is -> Decision making and Data analysis share topics so students cant do both -> Bus Finance and Corp Finance share topics so students cant do both
There is a table with 7 module options for each semester and their credit weight, either 10 or 20. The outline is deliberately vague, 20% of marks will be for supplementary features that will enhance the systems functionality and usability.
------------------ Based on the examples I have worked through, I see it like a basic ordering system:
I need a database that will let me input customer information (that I can do)
Then allow me to Create an order for the customer Using a form that lists the products available from the products table
When Creating the order, with the form, rules need to be enforced to ensure the order is valid (ie certain products are not chosen together, the products value adds up to 30 for semester 1 and 2).
Once the order is complete a report is generated to show the products ordered (modules chosen)
Is this the right way to look at the database design?
I see the module choice form as having all the modules listed and tick boxes, however the only order forms I have managed to find have drop downs, any pointers here?
Am I right in thinking it would be VBA that will allow me to set and enforce the rules on module selection? If so any pointers on how?
Is there a way to get VBA to validate the inputs on the order form in real time, eg grey out one selection if another is selected, not allow more module to be ticked than 30 credits. I guess the code can be looped to do this? or is it easier to check the inputs after ok is pressed then give a warning box if its incorrect.
Again apologies for coming in on my first post and asking a torrent of probably basic questions, but I have tried my books and the net and would appreciate any guidance
I'm kinda lost in here so I'd appreciate some help in pointing me in the right direction please. I've no doubt the answers are out there but I'm having trouble finding what I need, probably because I don't know how to frame my questions!
Basically the story is this - there are three guys covering various aspects of the business, they each visit three separate locations - all guys visit all locations. We have made a "big plan" that has 14 key deliverables in the locations as a "region" .
For each Key Deliverable there will be Actions, some Actions will be done in all locations, some might only be needed in one location. Each owner will describe the Action and link it to the Key Deliverable it supports.
What we want to do are things like Take Location 1 and Guy A and show all the things he is doing to support Key Deliverable X.
or
Show all the Actions in Location 2 that are being done to deliver Key Deliverable Y by all Guys.
That sort of thing - progress reporting and identifying where we have nothing happening to support a Key Deliverable or too many actions in one Location etc
We have all our plans in Excel spreadsheets at the moment, each guy fills in a sheet and at present the idea is that we copy and paste it into the mother of all spreadsheets to present to the Gods - it's not happened yet because it's clumsy and well, it's my job to present it and I thought "there's gotta be a better way!" I can import the sheets into Access but then it all gets a little tricky and I run out of tallent.
OK so if you've read this far you're wondering if I have any idea what I'm talking about when it comes to Access! Well I am pretty much self taught and rusty but I can see that Access could help us get organised and
My limitations - I'm using standard Access 2007, I doubt corportate IT security would allow me to add in any useful plug ins or upgrades etc.
Am I using the right tools? I'm limited to MS Office really so I mean should I stick to Excel or carry on pursuing an Access solution?
I am working on a project that requires data transfer using TCPIP sockets. I am using a 3rd party Library (Ostrosoft) that handles the wsock32.dll api calls.
The project calls for the creation of a header that logs into a user account.
The first part of the header is a marker, with a value of 4,275,878,552. It is to be supplied in a Binary format of length 4.
My quesion is "How do I create this marker".
What I have tried so far is a string variable - strMarker thus:
I hope (boy, do I!) that someone can help me with Access 2007 amd SQL. I'm a graduate student taking a course in databases. I'm NOT techie, but I figured my husband or kids would be able to help me - they can't! I am absolutely stumped and am hoping someone will be patient enough to chat with me about SQL and Access. (You CAN query in SQL with Access, right?) Thanks, Bev
Hi, I am currently stuck on making a working one to many table relationship, I will post what I have so far, what I am stuck on is forming a proper working relationship.
tblCustomers CustomerID Name Address(street) City Region PostCode Telephone Discount %
The desired output is a working order form, that when, for example you enter the product ID into the appropriate field automatically brings the rest of the product fileds such as description and unit price. And when the customer ID is put into the appropriate field all of the relative customer info is shown.
I am new to access and I apologise if my post is unclear or incomplete, any input is appreciated.
I copied my data from MSAccess which one of the fields is a hyperlink field into excel. Well, the field contains text reading "FileA" but the hyperlink is dbfilesFileA.doc. Since I have it in Excel now, I want to be able to just have the data read "FileA.doc" instead of FileA, I thought if I can get the field to read the hyperlink path dbfilesFileA.doc then I can maybe use the Mid formula to take out dbfiles....
Just upgraded to Acccess 2007 and have ownership problem extracting queries from a 2002 db.
When trying to import into a new 2007 or export from the 2002, get "you have no permission" message
Have created a new user tried to change owner of the db, but no permission File security looks correct in that I am owner of the file with full permissions, but Access doesn't recognize me as the owner
Tried logging in as admin, but it isn't the owner either
This is not critical, just very time consuming, this is a test project, but it would be nice not to have to rewrite about 30-40 queries and equivalent forms
Everytime I close, open, modify, etc, you get my drift,either a linked table or an in database table, Access validation takes minutes before I can continue.
I have a database that had some code that enabled the user to click a button and it would take information from one Access query or table and paste it into specific locations in Excel. This is important because the last worksheet has specific formatting in place.
If it didn't, I'd just use the transfer spreadsheet option and not even bother opening Excel at all with code.
I've got it working well except for the last part. The last query being copied has eight records and only one is being sent to Excel. Same code as the other queries where all of their data is going to the right spot in Excel with the right number of records.
Hello. My database has around 6000 products, and as time has gone by, the database has got bigger and bigger, more and more text - its now a rather large 16MB. Now, everytime i make a small change to it and upload to the server, it takes me about 8 min uploadeing it. Not that bad, but if i have to change 3-4 times a day? Also, it wipes out the website during the upload, which is not that great. Is there someway to compact the access database somehow? Thanks.
I am writing a vba procedure to updating some records in another Access database.
rsAccess.Open "SELECT * FROM AI_Table",conAccess, adOpenForwardOnly, adLockPessimistic
rsAccess!OCRExist = "Exist" rsAccess.Update
it has about 3 millions of records in that AI_Table. In the procedure, I perform some calculation and put the result into a TEXT(50) field in the AI_TABLE. As it was updating the records, I could see the size of the Access database file (the one contained AI_Table) grew very quickly, almost 1 MB/sec. I am pretty sure I am not adding that much data. If I stop the procedure and packed the database, it shrunk a lot.
I am just wondering if there is anything wrong with the way I am locking or updating the records.
I have a large .dat file which is run through an Access macro to produce reports. After a recent system change at work the format of the .dat has changed and now includes an additional bit of data which disrupts the macro.
I tried changing the extension of the file from dat to mdb to see if I could remove the additional column in access. I also tried changing it to a csv file as well but the file has a few hundred thousand lines and the csv file cuts most of it out.
Are there any other ways I can open this file in Access to remove this additional column of data?
I have tables and forms setup but I have 3 general questions on how to finish the project.
1. Should I create a folder where all the files reside for instance, create a phone log folder, put the DB file into the folder and export XML's into the same folder?
2. How do I get the form to launch as a self-contained form without the Access application in the background?
3. How can I get this form and table to automatically update an XML spreadsheet whenever records are added?
4. How can I get the form to sort all records according to time logged? The time will be entered in manually and we need all the records to sort automatically by time in ascending order.
Working on a database that tracks codes, descriptions etc.
One of the items they would like is a 'change' report.
On this report, they would like it too list 2 things. They would like it to list if any entries that refer to a expired edit type (determined by the end date field). Then if the code was updated to a new edit type it would display this too.
So the report would look something like this.
If the code that refers to a expired edit type, and has no update it would say code# removed <then list the edit type that expired>
or
If a new entry for that code was created with a new edit type, it would say something like
Code# Changed from <old edit type> to <new edit type>
Is this possible? I'm not even sure how to visualize it, so hard time moving forward with it.
I have two questions related to deplying an Access project I have been working on.
1. I started developing the project in Access 2000, but midway upgraded to Office 2003 and so the remainder of the project has been developed in Access 2003. Things seem to be fine, except that in the top most title bar, after the project name, there appears the following text:
xxxxx: Database (Access 2000 file format)
where xxxx is the name of the .mdb file.
Why is this appearing and should I have done something or do something to make it a Access 2003 format?
2. I am at the point of deplying the db to a small group of users (about 5). Some of them may have Access 2000 or Access 2002. Should I be doing something in particular to make sure things work in their machines. Any pointers to what may be problems or what I should do will be much appreciated.
I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.
However I have one issue that I am having issues with.
I have a query that determines a few collumns by running information through a vb function and returning the result.
The issue is I do not know how to convert this code from Access to SQL so that it will work.
The code is
SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Field, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later FROM TESTSTAT WHERE [LOG_NO] LIKE '*' ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);
Can someone provide me with a sutible sql equvalent to this code
I am working on a project that was started in access, but needs to be moved into an Access project so that It can be integrated with other forms. When I did this I realized that my database queries needed to be changed from Access to SQL code. for example instead of representing a date as #DATE# it needed to be represented as 'DATE'.
However I have one issue that I am having issues with.
I have a query that determines a few collumns by running information through a vb function and returning the result.
The issue is I do not know how to convert this code from Access to SQL so that it will work.
The code is
SELECT Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]) AS [Action], TESTSTAT.*, Action_date([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER])) AS Action_Date_Field, IIf([DUE]<[WAIVER],[WAIVER],[DUE]) AS Later FROM TESTSTAT WHERE [LOG_NO] LIKE '*' ORDER BY Action_Indef([DUE],IIf(IsNull([WAIVER]),[DUE],[WAIVER]),[LOG_NO]);
Can someone provide me with a sutible sql equvalent to this code
I have a data where I want to create a query fulfilling the below conditions. Suppose I have two table: Table 1 and Table 2 If a value ex.98 (Table1) matches with the value with 98(Table 2),it should pick up my second higher value 103. suppose 103 is the next high value of 98 . Please see the data value.
misprepaid.asmvalue from Table2 Required Result Con 989898 then 103 if value of table1=98 then 103 from table 2 (next large number) 103103103 then 149 if value of table1=103 then 149 from table 2 (next large number) 149149149 then 175 if value of table1=149 then 175 from table 2 (next large number) 175175175 then 198 if value of table1=175 then 198 from table 2 (next large number) 198198198 then 199
I'm trying to import an Excel file into access as a table so I can use the data in other tables I am building. When I try to import to file, I get an error message telling me that there are over 255 columns and not all my data will be imported. The file is a report I pull from another system at work and it is very large, is there a way to get past the 255 column limit?
I need to be pointed in the right direction if you don't mind. I understand underlying database structures pretty well, but I am not sure how to create a form in Access so the user can enter new data. I am tring to create a form based off of the classes table first and then go from there. I was hoping to be able to allow the end user the ability to enter all information from one form.
I have attached the relationship structure that I have setup but I am not sure where I need to go next. Should I create queries and then insert the queries to my form?
Ok first off thank you for anyone who even reads this.
Iv been assigned a project in using Access 2003 and only Access 2003. Im stuck and getting frustrated and was wondering if anyone would mind helping me.
What my Prof wants is a Access database with a form to do the following
Ok he wants us to input 1 of each category as if we were ordering
then he wants us to order it and print out a Recipte with the following info.
Sub total Tax Total Purchase How many Calories the entire order is.
Im attaching a .mdb file with what i have come up with but im not sure if its even going in the right direction. I would forever be in debt if someone would help me.
rename the wendys.txt in the zip file too wendys.mdb
I am a total NOOB when it comes to access....i am currently taking an access class at my college and its time for our first project. I really need help getting started cuz alot of the terminology is so new to me... I have been provided with a sheet of information and have to create a DB based on said information.
if anyone out there is feeling super generous with their time and is willing to help me through it...just to get me started..my aim is NIPPONLIFE86.ive found that its getting everything going that is the hardest...especially when im creating a database not just inputing data.:confused:
Does Access database support muliple users on the same data update, if so how many. What will be the best way to avoid locking up a field when somebody else is trying to update a different field within the system. It run on server:
am working on a Microsoft Access project for my computer tech class and I am stumped on a few things.
I have to do a payroll for all the employees on the project he gave us, I have to list their names, pay rate, and all tax rates when I put in another table. When I run the query and put all the requirements in the query and run it in datasheet view, it comes up with the employees name and every single type of payment and tax rates. So for IE, operations manager makes $18.75 a hour, but when I run the query it has 18.75, 15.00, etc for all the payrates given. How can I have it so just the pay rate and tax rate for just the person who is paid that??
I also have a Address book for all the workers. I need to group them by location of the stores(there are 3 store locations) and then sort the job location workers by last names, any help on how to do that??
I have the assignment scanned if anyone would want to see what all I have to do...
