Suggestions Regarding Table


I'm creating an employee database. This database will contain all kinds of information on the employee, e.g.; the Department & Division they are assigned to, their Position, their Pay and much more. Here's where I need your help. Our pay is composed of ranges and each range has 5 steps, example below. Our positions are assigned to a range and many positions can be assigned to the same range.

Rage A B C D E
43 2,000.00 2,100.00 2,205.00 2,315.00 2,431.00
44 2,020.00 2,121.00 2,227.00 2,338.00 2,455.00
45 2,040.00 2,142.00 2,249.00 2,361.00 2,479.00
46 2,060.00 2,163.00 2,271.00 2,385.00 2,504.00

I'll have an entry form to enter all of their info and within this form when I select a position from the position combo box I want other combo boxes to display range/step and pay for that particular position. One other thing our ranges are adjusted every year by a certain percent and if possible I would like to adjust all ranges just by entering that percent.

Thank you for your assistance.
Posted On: Monday 22nd of October 2012 05:56:46 AM Total Views:  251
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Newbie Query regarding Pages and Macros.

Sorry for this, it's probably an elementary error, but this is the first time I've used access for about 5 years... Anyway, I'm trying to create an access database for some colleagues who are not too IT literate. I've decided to create a UI of sorts by using the page function. The idea is that all the common tasks (searching through the data base, adding new records e.t.c) can all be made easier using macros that can be activate by buttons the page. I have the queries set up, the macros are ready to go, and the page layout is finished. There's just one small problem. I can't remember how to put the macro buttons on the page! Last time I did this was in school, but I'm certain there was a way to place a button on the page that ran the macro when clicked. Can anyone tell me how this is done If it helps, the version is access 2000.
VIEWS ON THIS POST

64

Posted on:

Monday 22nd October 2012
View Replies!

Questions regarding SQL syntax in MS Access

The background: 1. I will be working with Access 2003. 2. My back-end databases will be sitting within the .mdb file and powered by the JET engine (i.e. no connection to SQL Server, Oracle, etc.) 3. I will be writing SQL within my VBA code for purposes of automation. For example, querying the database and then using the information to update MS Office documents (Word/PPT/Excel). My question: When I write the SQL code within my VBA, does the syntax have to be JET SQL for it to work If I use T-SQL, for example, will it fail The follow-up question: If eventually I migrate the databases from MS Access / JET to SQL Server or Oracle, will I have to rewrite my SQL to follow the correct syntax I.e. converting JET SQL to T-SQL
VIEWS ON THIS POST

69

Posted on:

Monday 22nd October 2012
View Replies!

Make-table queries for Web?

, I'm using MS Access 2003 for a web DB... Am wondering.. is it bad to use Make-Table queries for the web.. Or should I just do all the SQL joining and linking stuff just with more SQL code Just curious about the impact Make-Table queries have on the Web.. Slower Not a good idea
VIEWS ON THIS POST

132

Posted on:

Monday 22nd October 2012
View Replies!

Adding data to a table

hi I had a form with TO,DATE,BODY and a button . on click of a button this fileds should get added to a table. I have created a table with same fields. Thanxx
VIEWS ON THIS POST

243

Posted on:

Monday 22nd October 2012
View Replies!

Locking a table based on query results

I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field Something like, if field1 = "expired" then lock the table I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
VIEWS ON THIS POST

561

Posted on:

Monday 22nd October 2012
View Replies!

Updating a table.

This is the code: Code: Public Function FOB() Dim sku As String Dim invoice As String Dim strSQL As String Dim dbs As Database Dim sku_rst As DAO.Recordset Dim invoice_rst As DAO.Recordset Set dbs = CurrentDb Set sku_rst = dbs.OpenRecordset("SKU_ TABLE", dbOpenDynaset) Set invoice_rst = dbs.OpenRecordset("INVOICE", dbOpenDynaset) strSQL = "UPDATE INVOICE RIGHT JOIN [SKU_ TABLE] ON (INVOICE.SKU = [SKU_ TABLE].SKU) AND (INVOICE.DIV = [SKU_ TABLE].DIV) SET [SKU_ TABLE].FOB = 'Fob' " & _ " WHERE ((([SKU_ TABLE].FOB) Is Null) AND ((INVOICE.ACCOUNT) Like " * 11010 * " Or (INVOICE.ACCOUNT) Like " * 11111 * "));" End Function I receive an type mismatch error. Run time error 13. I've checked the field types and length, correct.
VIEWS ON THIS POST

87

Posted on:

Monday 22nd October 2012
View Replies!

Doubt about a database with single table

Hi all, I have joined this forum recently, and would require the guidance of experienced members regarding a doubt: I have tried to develop an access database with only one table in our office intranet. The front end has been developed on asp and is running on IIS 5 deployed on Windows XP machine with 512 MB RAM and P-4 processor.The system is working fine. My doubt is ,will it matter, if more than one user is working on the database(with only one table).that is to say, suppose, one user is posting data to the table and another user is querying the table, will it lead to any complications The situation as such is low concurrency, maybe about four users will be working simultaneously at the same time.
VIEWS ON THIS POST

139

Posted on:

Monday 22nd October 2012
View Replies!

Append query to different table

Friends, I would like some help with a code that allows me to run an append query only if a specific field is not already stored into another table. Example. I add a new customer to my database using table1. I type his SSN. The code should check if this SSN is already present in another table (table2). If yes, so the code should skip the append query and just update any modified record included the SSN. If not, the code should run the append query. this should be done in the background while working in a form with its recors source to the table1. Can anyone help me
VIEWS ON THIS POST

422

Posted on:

Monday 22nd October 2012
View Replies!

Append Query to table

I have created an append query. Inside that query I have one column that take a result from another query and I have about 10 columns that follow that one column by infomation from a table. I do have the query and table "join properties" at "Student-ID". I am appending to a blank table but its not putting any other information into the table. The only time it appends if there is information in there. Is there a way to append to table with no records
VIEWS ON THIS POST

64

Posted on:

Monday 22nd October 2012
View Replies!

Newbie Question - Repeating fields in tables

If you are going to enter data via a form, just make a combo box tied to Title, with the four choices in its source list. Ditto for the other fields you mention. I'd just let Access create an autonumber and use that as the primary key. Then put an index on the Company that allows for duplicates. Hope that helps, David
VIEWS ON THIS POST

91

Posted on:

Monday 22nd October 2012
View Replies!

Data succession-passing db tables to new operators

I am preparing a database application that will be used by one person in an organization. Frequently , (every year or two) the program will be given to another (usually a laptop) user in the organization as that person assumes the task of the programs operation and responsibilities. I would like the user to load the program from the installation CD (made with Wise Installation System and SageKey softwares) on each computer each time there is a brand new user and each time a subsequent user takes over. However, each successive user needs to have the data files passed from the last user. At face value it seems that having a front end/back end arrangement might be the most deable. Can anyone jump start me on the best way to accomplish this process to EZ data transfer
VIEWS ON THIS POST

110

Posted on:

Monday 22nd October 2012
View Replies!

Query Not Updatable

I have the following simple query. The query joins the data in two tables and I use it in a bound form. This has worked fine for a couple of years, but today the query is saying it is a non-updatable recordset. This stops users changing any data in the form. I have tried the following to no avail: compact/repair recreate the query copy the whole database into a different MDB file Is there something silly I might be missing\t I did not changed any of the table structure this problem has just appeared. Code: SELECT tblWorksOrders.*, tblLocations.* FROM tblWorksOrders INNER JOIN tblLocations ON tblWorksOrders.WOLocationId = tblLocations.LCNIdentifier ORDER BY tblWorksOrders.WorksOrderNo, tblWorksOrders.InstructionNo;
VIEWS ON THIS POST

45

Posted on:

Monday 22nd October 2012
View Replies!

Compare 2 tables where NOT EQUAL

It need to compare two tables where the ID Number is not equal. Specifically I need to update new poducts in a table, but only those ID#s that are not already listed. I know how to compare two feilds where they are not equal... i.e. [New_Table].[ITEM_NUM][Old_Table].[ITEM_NUM] ...but I'm currently limited by the join properties in the tables, since I'm comparing IDs and not the feilds. ANY IDEAS
VIEWS ON THIS POST

98

Posted on:

Monday 22nd October 2012
View Replies!

2 questions 1, move record to a new table 2, prevent changes to design

1) HOw can I move a record from one talbe to another when a specific drop-down option is selected. Example: a combo box with "Active"; "Pending"; "Closed" I want all closed recoreds in a table just for closed records to reduce clutter. They're not going to be referenced often if ever, we just want them so we have them if we do need them. 2) How can I prevent changes to the design of a form/query/report/etc.
VIEWS ON THIS POST

110

Posted on:

Monday 22nd October 2012
View Replies!

Max amount fields in a table

What is the maximum amount of fields/columns a table can hold in access 2003 And what do you do if you have a form and its record source is a table that has run out of fields/columns If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fields. I'm currently waiting for my access 2003 upgrade disk to arrive and I'm anticipating issues that might occur.
VIEWS ON THIS POST

149

Posted on:

Monday 22nd October 2012
View Replies!

Users unable to access new forms/tables

I enabled security on an .mdb which included several users that are in the Full Data Users group. After I did this I needed to add a new form to the application, so I logged into the application and added it. However, though the admin group and the Full Permissions group can access it fine, the Full Data Users group cannot. I have double checked all of the settings and have even rebuilt the .mdw file to no avail. Any thoughts
VIEWS ON THIS POST

122

Posted on:

Monday 22nd October 2012
View Replies!

Adding combo box data to a table

I have an existing FORM called Evaluations where you can select a trainer and a trainee from two different combo boxes. Once a name is selected from both of the combo boxes I click on an assign button and I want it to record those two names selected into my evaluations table in the Trainer_Name and Trainee_Name columns. How do I do that\t Please help\t
VIEWS ON THIS POST

250

Posted on:

Monday 22nd October 2012
View Replies!

Updating a table in Access using VB

I have a form that updates a table. It's got some convaluted logic (I didn't write it I swear ). I am trying to add some VB code so that when the form closes, the table (TableA) opens, all records are selected, and any commas are replaced by periods. The table should then be saved and close. I know how to open, select records, save, and close, but I can figure out how to get it to do the find and replace. Any takers
VIEWS ON THIS POST

68

Posted on:

Monday 22nd October 2012
View Replies!

how can i join these tables?

there must be a way to do this, but i haven't been able to figure it out yet... table one has three fields i need to be concerned with, member_ID, range_start, and range_end. no part of any range spanning range_start through range_end will be duplicated. table two has several fields i need, but the field of primary concern is a person_ID that will fall somewhere within a range designated in the first table. how in the world can i join these tables, since none of the IDs in the second table are actually listed in the first, but fall in between values i need to get member_ID from table one, person_ID and several other fields from table two. any ideas
VIEWS ON THIS POST

81

Posted on:

Monday 22nd October 2012
View Replies!

Link failure to MySql table

I have a table sitting on a MySql database. I want to access that table from within Access. I have set up an ODBC DNS for the MySql database, and everything is fine, except for one really large table. Whenever I try to open that table, or query that table, all the fields show up with "#deleted". If I attempt to add a new record, the data does go into the linked table, but Access shows "#deleted" as soon as focus leaves that record. If I import that table, instead of linking it, the data comes across just fine. Any idea what I should look at to try to fix the problem
VIEWS ON THIS POST

75

Posted on:

Monday 22nd October 2012
View Replies!