Hi, All
I am designing a database to keep track of part prices and their trends, the part numbers and all their related information are stored in a "Unified Parts Database" containing all pertinent information about the parts, their vendor, current price, material, sub-material etc...Now in the new database I am making I wanted to have one table that has these field.
The tables name currently is "Main", fields are as follows:
and then I have a "Prices" table which stores the different price changes for each part number:
PriceID (PKey)
PartNumber
Price
PriceChanged
now I what I am trying to do is have the "Main" table pull all the part numbers and their descriptions from the Unified Parts Table which is a linked table and I am not sure how to get it to fill all this stuff automatically inside a table not a form, would a "Main" Query be the correct way to go and completely skip the "Main" table??
I have constructed a seven table database for dealing with holiday cottages.
The seven tables are:
Bookings Cottages Customers Ratings Owners Regions Sales Reps
I have created the 7 tables and created the primary keys (BookingID,CottageID,CustomerID,RatingsID,OwnersID ,RegionsID,RepID)
The ratings table because cottages come under different ratings (Family, Sporting etc..).
I need to create a form which
*Adds a new booking, and also adds the new customers details *Checks the avalibility of the cottage for the chosen week *allows the user to choose a rating from a combo box, which then filters the cottages availible for selection in that category.
If anyone could help me with this I would be very grateful, I have emailed my lecturer but he is not replying and the assignment needs to be in on the 12th!
While searching for Access help through Google, I found your forum which has greatly helped me in building my database in Access. I got all answers through your forum for many of my doubts and ‘How To’s. I thank all the members for their excellent and simple way of explaining for a novice like me.
As to the Database I built, it is almost complete and working fine. I have built a switchboard with menu choices. I have distributed this to all my colleagues and they are using fine.
I have three problems still to be addressed. I have tried to protect my database tables, forms etc through the security wizard, user permissions etc. But nothing is working. I have hidden all tables, forms etc. and unclicked the ‘Display database window’ in Startup so that only the switchboard is visible. I have also changed the switchboard properties to cover the entire screen and no maximize/minimize/close button. When a user double-clicks the desktop icon the database file opens with the switchboard menu. There is an Exit button in the switchboard which the user can click to he can click the close button of the Access window. Apart from this, the user can do nothing to corrupt the tables, forms etc., so I believed. However, one colleague clicked the ‘View – Design View’ in the Access menu bar and the Switchboard became ready for modification. Is there a method to block the View – Design View’ option in the Access menu bar?
My second problem is that we want a new database file first of January every year. Is there a method to create a new DB file with the same table, form and settings from within Switchboard? (The only option I found was to copy the DB file to another location, delete all records and rename it for the new year). Is there any other procedure?
My third problem is that I designed a Crosstab Query as given in the Help menu with criteria LIKE “*” & [Enter any character to search by: ] & “*” but when I execute the query it displays all records containing the character entered. This is not what I want. My search criteria everytime changes (it is text based). So I am unable to save Query form with a specific criteria as it will execute only defined parameters. Is there a way to make Query form where any user input (i.e. any word or number) is taken for search?
Hey guys , I was wondering if you can help me with this one cos I am clueless. I am in charge of updating this website which sells stationeries, office equipments etc. Well the catalogue in our website is kinda messy since there are lot of the product items inside the catalogue page which has no images.It is embarassing for those customers who wants to buy a product but couldnt see the picture of the items. For example , http://www.mellon.com.sg/catalogue/s_searchresult.asp?searchby=category2&searchfield=laminating%20materials.
The product items are generated from the Access database where I also included a column field in the database called "Picture" where those product items with images have checked(tick) in the box and those without images are unchecked(no tick) in the box I am not sure if I should filter anything or set up any criteria for the pictures from the database. I need help as i am not a total expert in Access
I'm an audio/video technician at a college and wish to use Access for a simple camera booking system, but do not have time to go through all the extensive help menus and tutorials about this program, and none I have viewed seemed to help.
I want four columns in the table. First, the user inputs a student ID number. Now my first problem is that I want the student name to appear in the 2nd column after entering their number. I don't know where to create the list of names or how to link them to their student ID numbers.
My second problem is I would like the 4th column simply to display the date seven days from the current one (as in whenever the db is used) for the return date without having to input it manually.
Any help towards these 2 problems would be most appreciated, and thanks in advance.
I want to make an access database but Im struggling with the relations a bit. I understand the one-to-many and many-to-many concept but seems like there is more then that
I want to make a database that keeps track of things we lend.
I have several items with different properties we lend out:
eg
TANK -ID -Volume -Weight
MACHINE -ID -hours -fueltype
...
I made the following tables to keep track of it:
RENT -ID -Customer -...
One-to- many relationship between these
RENTLINE -ID -RENT_ID -Item
My problem lies with this Item. I want this Item to have a relation with the ID of the different items we lend out. there needs to be a one-to-many relation here. one rentline can exist of one TANK OR of one MACHINE. A TANK or a MACHINE can exist on many orderlines. So I made 2 one to many relationships from Rentline to the id's of Tank and Machine
the problem is that Access does not let me fill in the rentline with the id of a item. If I fill in the ID of a TANK it says a ID of a machine is needed and vice-versa.
I tried to solve it with an intermediate ITEM table that looks like this : ITEM -ID -Sort ( Tank or Machine) -ItemID (Id of the tank OR the Machine)
But this generates the same issue as directly in the rentline...
I guess this is pretty common stuff Im asking here but its confusing me like hell :(
I would like to make a human Resource database that shows in a tabbed form some informations about our employees.
I would like to show per employee: tab 1: general info (name, address, starting date, type of contract, department) tab 2: salary history tab 3: capabilities -whether they are available YES/ NO tick field and for the department the person is assigned to
I am thinking of the following tables: Employees - EmployeeID, Name, address, starting date, type of contract Department - DepartmentID, Capabilities Salary History - EmployeeID, Date, Salary, Notes EmployeeCapabilities - EmployeeID, DepartmentID, Capabilites [Yes/ No]
The only problem I have is to make the correct relations between the lot of them.
here is a link to my website http://www.justevents.biz if there is anyone who would be kind enough to look at it and tell me how to build an access database for the categories and search by zip, etc. i would be greatly appreciative. I have tried all the tutorial helps and built about 15 different databases and can't get a single one of them searchable under any circumstances. I have tried all I know to do.
I want the value to be displayed in a combo box from database , when the form_load is in action
The code is
Private Sub Form_Load() Dim s As String
Set con = New ADODB.Connection con.Open ("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:stock.mdb") Set rec = New ADODB.Recordset rec.Open "instbl", con, adOpenDynamic, adLockOptimistic, adCmdTable While rec.EOF = False s = "select section from instbl"
I want the value to be displayed in a combo box from database , when the form_load is in action
The code is
Private Sub Form_Load() Dim s As String
Set con = New ADODB.Connection con.Open ("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=d:stock.mdb") Set rec = New ADODB.Recordset rec.Open "instbl", con, adOpenDynamic, adLockOptimistic, adCmdTable While rec.EOF = False s = "select section from instbl"
I am looking for a database that can keep up with the full management issues relating to single family houses. Some are on a lease. Some are on owner-financed by use. Any help would be great!
I am willing to pay a designer to program this database for me. Email any samples or questions to babyfacedavid@yahoo.com
i need to design a database but am abit unsure the best way to go about it and am looking for sum thoughts and suggestions.
I need to create a database for part numbers and competitior cross references, however the problem i have is were we list one number a competitior could list upto 5, and where we list 5 numbers a competitior could list one, so there could be literally thousands of duplications per competitior both ways around.
for instance we list DG897, our main competitor would list AB458, AB459, AB 487 etc. Basically the part number is a car part, and we say one part number fits serveral vechicles, but out competitior says its one part per vechicle. (sorry for the poor explaination)
has nebody came up against a database where the possibilty for duplications is so high before and how did they go bout designing the tables, I am far far from a regular user of access, however am very keen to learn.
I am quite new to access and i can grasp things quite easily.
Basically i am make a stock control data base for a food company. I am making it so theres an order table and a stock table. Basically i want to know how to change the Quantity avalible in the stock table fom the orders table.
I dont know if i have explained that very well. But basically i have related the product name in the stock table to the product name in the order table. So i can add a number so that that the quantity of the product will change on the stock table.
If there are any easier ways of doing this rather than having 2 tables it would be useful
Ive tried all types of things and i cant get it to work. Any help would be very much appriceated.
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?
Hi, I would like to get your advice on my table setup and relationships for this payroll project. The company is an engineering company with Projects (or construction sites) around the world.
The 'Candidates' are current or potential employees and contractors. There are three main pay categories:
1.Shift-workers All shift workers doing a particular job on a particular project are paid the same rates e.g. all welders on a particular project or site in England are paid the same as each other. For that reason I want to link the pay rates with the job description for these workers. This avoides creating 50 records for 50 welders on the site in England to say that they make £10 an hour normal time (or whatever it is) etc.
2. Contract Contract workers usually get paid a flat rate per hour. As these are negociated on an individual basis I would need to have this information linked to each individuals job (M_CandidateJobDetails).
3. Salary Again this information needs to be input for each individuals job.
For the contract and salary people the pay frequency can vary (weekly, bi-weekly or monthly). So can the currency they are paid in. I haven't got as far as the currency issue yet.
The reason for the one-to-many relationship between M_JobClassifaction and M_CandidateJobDetails is that many candidates can have the same type of job e.g. there can be many employees that in the job classifaction of 'Electrician'. For many of the jobs at managerial level e.g. 'site manager' there will only be one.
I will have a table with the hours worked by each person per week. I can use this for those on shift work or contract to calculate what they will be paid.
One of the main reasons for this database is so that the company can print reports to see what is paid out in payroll for each site and in total (in euros). These will be gross figures and I don't need to take expenses, vacations, bonuses or taxes into account. They other thing we will need to be able to do is assign candidates to vacant positions and change them from one position to another - possibly between different projects.
So basically does anyone have any comments on the relationships, normalisation or anything else. Is this the best way to do it?
Hi, I am new to this post. I am a physician with interest in database designing. I have been trying to design a database for my clinic for few months but am unable to make one. I have been searching/ reading alot of info and came across this thread. Maybe someone can help me. Actually, I want to make a database regarding ultrsound scan examinations of patients. I have five tables. 1. Patients. (patinetid*, patientname, age, sex, address, contact no) 2. Physicians. (physicianid*, physicianname, speciality, address, contact no) 3. Scans. (Scanid*, scanname, charges) 4. Scan orders.(scanorderid*, patientid*, physicianid, scanordernumber, scandiscount, totalcharges) 5. Scanorder details.(scanorderdetailid*, scanorderid, scanid, charges, discount)
I want to have primary key for scanordernumber which wil be the patient number and should this be placed in patient table?? All the ids have been linked with one to many reltionships. Actually I am unable to set proper relationship. So when the patient arrives he is registered with a unique number, a physian name with date added and scan ordered is entered. Sum calculated. I have done the later part with the form all designed but the relationships and primary key are all messed up.
I can post an image of relationships or blank database.
The recruitment database I have designed for work is okay in the main except for where I need to filter candidates depending on their skill sets. What I need to be able to do is filter candidates that possess ALL skills selected in a multiselect listbox...
Table structure runs as: Candidates (many) linked to Job Role (one) (because candidates can only have one job title (in theory anyway) Candidates linked to Skills table via junction table (candidate having many skills / one skill belonging to many candidates blah blah)...I'm sure this set up is as normalised as I can get (I aint no expert tho)...
My solution so far (suggested by another forum) was 3 listboxes on a form that runs like... ListBox 1 = Job Role (Manager, Team Leader, Clerk, etc - set to SINGLE SELECT because an employee can only have ONE job title (supposed to anyway LOL)... ListBox2= Skills (Payroll,Audit,Taxation, etc - SET TO MULTISELECT because employees can have more than one skill)... ListBox3 = Candidates (populated by making selections in ListBoxes 1 and 2)
It all works well but is VERY slow as I was told to use make table, append queries and quite a bit of VBA to make the WHERE clauses as SQL statements, etc
I've searched high and low all over the net and have found things that come close but I'm just not adept enough to work it out (I started Access late in life). I feel I need some kind of subquery that first of all finds all candidates that e.g. have ALL 3 skills selected in Listbox 2 (creating a recordset of one row per skill meaning each candidate is listed in the recordset for as many skills selected and then filtering again with a count function that only displays candidates with a count of 3 skills - this subquery would then be used to populate Listbox 3 -
Sorry if I've overcomplicated this but it seems such a simple thing to and I'm getting a lot of pressure at work having being trying to solve this for weeks...
Any help putting me in the right direction or if you know of any similar example databases that would help me learn more would be much appreciated
Hello Everyone, I am in the final stages of creating a database for school coursework however I am having a few problems with a few features; any help would be much appreciated. The database itself is meant to manage / keep are record of utility bills, it consists of two tables: Invoice and Company.
Problem 1. In the Invoice table there are two fields called Date Due and Date Received the data type is Date/Time, how do I create a validation rule so that Date Due must be after 'Date Received' and vies versa?
Problem 2 I want each record in the database to store an image of a scanned invoice, and the main form called Transaction to show a preview image of the invoice. Much of the code I used is borrowed from the Access Northwind sample database, after some initial success I had to redesign the 'Transaction' form where the picture was shown and now I get the error message: ‘Run-time error 2135, this property is read-only and cannot be set’ I think I know what is causing the error but I have had no luck fixing it, if someone could help me that would be excellent.
Problem 3 Once I get the Transaction form to properly store and display a preview image of an invoice, I want to add a button that opens the image in an external window(ie MSPaint). Currently my only success has been to add a button on the form that launches MSPaint (you have to manually open the image in MSPaint.) Again, if someone could help me sort this out it would be excellent.
Sorry if my problems aren’t clear or well explained.
I have spent many hours looking for answers to my problems in forums, Access help files etc… My computing tutor has little (no) experience with Access so he has been unable to help, hopefully the people on this forum have the expertise.
I am happy to email you my database if you need it to help me with problems 2&3.
Hello Everyone, I am in the final stages of creating a database for school coursework however I am having a few problems with a few features; any help would be much appreciated. The database itself is meant to manage / keep are record of utility bills, it consists of two tables: Invoice and Company.
Problem 1. In the Invoice table there are two fields called Date Due and Date Received the data type is Date/Time, how do I create a validation rule so that Date Due must be after 'Date Received' and vies versa?
Problem 2 I want each record in the database to store an image of a scanned invoice, and the main form called Transaction to show a preview image of the invoice. Much of the code I used is borrowed from the Access Northwind sample database, after some initial success I had to redesign the 'Transaction' form where the picture was shown and now I get the error message: ‘Run-time error 2135, this property is read-only and cannot be set’ I think I know what is causing the error but I have had no luck fixing it, if someone could help me that would be excellent.
Problem 3 Once I get the Transaction form to properly store and display a preview image of an invoice, I want to add a button that opens the image in an external window(ie MSPaint). Currently my only success has been to add a button on the form that launches MSPaint (you have to manually open the image in MSPaint.) Again, if someone could help me sort this out it would be excellent.
Sorry if my problems aren’t clear or well explained.
I have spent many hours looking for answers to my problems in forums, Access help files etc… My computing tutor has little (no) experience with Access so he has been unable to help, hopefully the people on this forum have the expertise.
I am happy to email you my database if you need it to help me with problems 2&3.
I need some help on a database that is set up for work use.
It's rather large and I have no technicial knowledge of access whatsoever so an answer in plain english would be appreciated.
I have attached a picture of the screen that i am having the problem with. The blank fields contain peoples names and addresses. On this page i have to click on "update service user" on the first user, the database opens a seperate page and i enter a few details, then i click to go back to this screen.
When I return back to the screen, the scroll bar is at the top of the list. This is OK when I am doing the first few letters of the alphabet (the users are listed alphabetically) but when i have too use the scroll bar and choose the letters later down the list, then click to update then return to the screen and the scroll barr is not where i left it...ie: it's at the top.
So what I am asking is, is there a way of freezing this scroll bar so it will stay there even when i have navigated away from the screen to update each user?
If someone could answer me this query then I would be most grateful as it's quite time consuming for me to use the scroll bar and find the next person that i need to update.
Hi, looking for help big time!! I am a novice at using MS Access and struggling like hell. I have been set a project at University that requires me to import data from Excel, sort the data (i.e the data imported will be for a whole year and I am only interested in looking at single months), use calculations on the data imported and display this graphically. Is this possible with Access?? Any help will be gladly received as Im desperate.:(
I have a multiple user access database on share drive with user-security level created. I created myself as administrator, and remove default Admin from Admin group. Removed permission from user group and joined workgroup file which I created as "MBS work group.mdw". I created shortcut on user's computer. I believe the syntax is correct
"C:Program FilesOffice 2003OFFICE11MSACCESS.EXE" /wrkgrp "\ServerNameMBS DatabaseMBS Work Group.mdw" "\ServerNameMBS DatabaseMBS.mdb"
Everything works fine. When I directly double click the database (MBS.mdb), it pops up a login box. However, when other user double clicks the database directly (not from shortcut) from their station, login window doesn't pop up. It allows them to access the database. I saw the work group file used default System.mdw
My question is how can I restrict them by double clicking the database without go through shortcut? Why my database security not protect user access without give user name and password. I tried by joined user’s work group file to MBS work group.mdw. It works, but I don’t want let user manually to do that one by one.
Is there anybody can help me go through this problem?
Sorry, i dont know where should i post this( under which category)
so i just post it here,
I want to create a login page using access, and then i wanted to record down the name of the user and the time and date(login and log out) when he/she is using the database.
I am fairly new to DB's and have been trying to get my head round a database I want to build. I have had a few pointers from other people on here which has helped etc.
I was wondering if anyone out there would mind helping me with this project. I will go into detail if anyone is interested. I am hoping that someone would let me add them to messenger so when I get stuck etc, I could drop them a quick note. I am not going to harrass you every minute of the day (I promise not to try to anyway :D ). I would like to work with someone so I dont have to keep explaining it to someone different each time.
If this is something that someone wouldnt mind doing, please, please, please let me know.
