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 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.
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 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 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?
I just want it to deduct: one numeric field from another - to get a miles travelled number ? one currency figure from another - to get how much more/less fuel was used than last month ?
also to work out: an average of 3 figures to multiply one figure by another ?
Is this possible ? How easy is it ?
This is because I want to move my Excel spreadsheets to Access so I can alalyse it better and create custom reports. But despite browsing Access for a while, I can't find the features I need, or anything in the help file to help me.
I've been using Access for a few years (Access 2000) and have created two custom databases from scratch.
Jay Evans IT Support Co-ordinator for Simply Drinks Ltd
Here is something I wonder if it has already existed or been asked before, a program or an add-in feature that can compile an Access database (including forms, reports, tables...) to a stand-alone .exe application, just like Visual Basic does. The reason why I think it's doable is because in Access, we use VB codes anyway and it wouldn't need to be rocket-science for Access to do something like that or this would be an improvement for future versions.
There are plenty of advantages of going from an Access form to a stand-alone app, i.e. we wouldn't need codes to hide background and menu or status bar; we wouldn't need to disable mouse-scroll or prevent users to go back and accidentally delete past records; or we wouldn't need to worry about users seeing your terrific coding techniques and on and on.....Agree that all these can be done within Access but imagine how much time it'd save you from not doing all these things.
ok I'm an accounting major and for one of my classes which is accounting information systems, they are making us design a database in access. Long story short, our teacher is horrible and is not teaching us access and continues to assume we know it and ignores us when we explain to her that access was never taught in our lower level classes. Our book also does not cover access to an extent that will help us with this project.
I have a small access assignment that requires we make a database for a small video rental store. The only things that need to be kept in mind are that the store has many customers, you stock over 2,500 different movies with most having multiple copies. All daily transactions need to be managed.
I have a relational database done.. I'm just asking if anyone feels like looking at it and pointing out some mistakes or areas that should be worked on or improved I would really appreciate it.. Thank You in advanced!
If you have an Excel workbook that has a connection via a 'connection' to an Access database, do you have to have the Access software itself or is there some other way to get to the data? Idea is to limit the number of licenses that need to be purchased. Thanks.
There exists a many to many relationship between tblMODELS and tblFAA becasue once this problem is understood the database will be expanded. On my mainfFAA form I have the subfFAA/MODELS form which is displaying the information correctly. I also have a command button which properly opens the mainfEDIT_FAAMODELS form. However, when I open the form via command button it does not display the models listed in the subfFAA/MODELS. I know I can use the wizards to make this work properly but would like to understand the underlying requirements the wizard is inputting to Access.
Furthermore, I want to be able to add models with the mainfEDIT_FAAMODELS and not with the subfFAA/MODELS. I want to do this without displaying the MODELID or the FAA APPROVAL ID fields due to the fact they are useless information. I am sure this can be done via SQL but haven't been able to troubleshoot the syntax correctly.
Lastly I want to filter the ENGINE and MODELS comboboxes in my mainfEDIT_FAAMODELS based on the MANUFACTURE combobox (cascading comboboxes). I know there are many threads regarding this subject but I cannot seem to adapt one of the solutions to work with this database and do not know why.
I am not a programming wiz so please be basic. I think the biggest obstical I am having now is the syntax for filtering, sql, etc. Thank you in advance for your help.
Due to me still trying to grasp the basics of access, I have what I call the "Command Centre" which is basically a form where our administrators verify all records placed and control them from here.
As different stages of each record requires Authorising/Further Authorising by other users, they will require to update the same records as displayed on the admin control from.
The problem I have is that when the administrators make a change to a record which prompts an authoriser to go into the db and authorise, if the admin users keeps their form open (which is what I want), I understand that it doesn't save changes to the record until they exit the form. Naturally this causes problems for the authoriser.
Is there a way of saving changes to records as they are made so other users can edit the records as see it up to date. I'm thinking along the lines some vb code to save on an action.
hi... i have a form that display things in a table in access. when program the form to delete a query it deletes it but does not do rs.movenext what i want to do is the form to display the next set of data from the database. i am using dao.... please help me!!
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...
Table 1 contains telephone numbers and rates per minute Table 2 also contains telephone numbers and rates per minute
I am trying to check the rates in one table against the other.
The problem I have is that the telephone numbers in each table are of differing lengths.
For example ...... Table 1 contains telephone number 01234 567890 Table 2 contains a rate for numbers starting 01234 5
I have split the numbers in Table 1 in to 0, 01, 0123, 01234, 012345, 0123456, 01234567, 012345678, 0123456789 and 01234567890 but I am stck on how I can now use this to match to data in Table 2.
In 'words' I would like the database to 'say' ...
0 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 01 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 012 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 0123 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 01234 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for ..... 012345 in Table 1 - Is there a match in Table 2, if so what is the rate? If not then look for .....
etc etc etc ....
The numbers in Table 2 are varying in length.
Can anyone provide guidance on database design and / or help with writing queries ?
A simple personal database created by me is to be deployed on a home network having two PCs. I did splitting to FE and BE. The BE shows only tables. I would like to have the guidance of experts in this forum for the following: 1. Whether BE to be copied first in the host PC? 2. FE to be copied in the other PC? 3. After copying the Access DB how to link FE and BE between the two PCs? 4. Whether data entry, edit, search etc. possible from both the PCs? 5. If I test with dummy records, how do I delete dummy records, from BE or FE? Shall be grateful for help.
Good Morning, I am new to Access and I welcome expert guidance on the relationships of my database. The database will be used to track employee training and although I've enjoyed limited success so far I'm hoping that eventually I'll get it right.
The database has to do just a few things so it doesn't have to be very complicated. It basically has to store employee, training event, and completed training event information. The attachment shows the tables and their relationships.
The are basically two types of training that the database needs to store. The first is training that applies to everyone and the other is training that only applies to certain job specialties. These types of training are identified by the ProjectMandatoryCode and the JobCode respectively.
The ProjectMandatoryCode is a default value that is automatically entered everytime a new employee is entered into the database (EmployeeInfo table) and it is also a default value when a training event is entered into the (TrainingEventInfo table) that applies to everyone. The employee JobCode is a selection when a new employee is entered into the EmployeeInfo table.
When the user wants to enter a training event that is job specific, they are able to select a JobCode from a combo box/option group and this code is written to the record. Selecting one button from the option group writes a default value (ProjectMandatoryCode) to the TrainingEventInfo table while selecting the other button enables the combo box so that a specific JobCode can be entered against the event.
Everything works fine except... so far I've been unable to produce a report that lists every training event that pertains to the employee whether it be ProjectMandatoryCode or JobCode related. I can query and produce completed events or a list of what events are required but nothing that is complete. Ideally, I should be able to produce a complete training plan for the employee which shows all necessary events and where the employee has completed the task I should be able to show the DateCompleted.
This failure has led me to doubt my table relationships since all my efforts at various query joins have failed. Is there anyone who could enlighten me so that I could learn from this, complete this task, and apply this new knowledge to future databases? To the person with the answer, thank you in advance for your assistance. Respectfully, Dale Gagnon
Hey everyone, I'm new to access, I have been using Excel for quite a while and I am familiar with VB and macros.
I have never been much of a book learner, mostly hands on, which means a lot of reverse engineering to figure out why/how things work.
I have a scenario, and found a DB that had some functionality that I liked and I had reverse engineered it to do a lot of what I want.
Where I need some guidance:
1. on my opening form (Clients) I would like to be able to type in Client Number and have it return the correct info.
2. in my Who Has It is there a way to do data validation (or dropdown box) I mean I only want them to be able to enter valid names not crazy stuff like MickeyMouse.
I have an access Query(named newSerial) : SELECT TOP 1 (Productbase.Serialnumber+1) AS Expr1 FROM ProductBase ORDER BY (Productbase.Serialnumber+1) DESC;
I want to access this new value. Its not a key since an autonumber may be any number. When I create a new product. How do I get this value inside a form? It works fine when clicking on it. But if I use an unbound box I get 1 as result and not like 76067 which it should be. If I click the query in access it works fine and show 76067. I tried: outbox=Expr1 ' gives null 'or outbox=[newserial].[Expr1] ' which gives "Access cant find the field "|" refered to you in your expression"
What should I write? Whats the correct expression?
