hi everyone,
as a network engineer, it administrator, etc etc people expect to know absolutely everything about pc's. so here i am.....
a company whose network i look after are using excel spreadsheets on a weekly basis to take orders, wages, running costs etc from their remote sites (currently about 20 sites)
the spreadsheets are accessed via terminal services and are moved via a script at the end of the week so the remote site doesn't have access to the old records and replaced by a new sheet. so lots of spreadsheets floating about.
you can imagine how hard it is to pull past records & compare etc.
now, i'm new to databases and have order a book to gen up on the subject. the sites should only be able to edit the last 7 days of records but be able to see the last years. i can see how a query and subforms can achieve this etc.
each site shouldn't be able to see the other site's records and the head office should be able to run reports etc on each site or as a whole.
i can narrow the time down that everybody is accessing the data. the sites may grow to 50+ but the head office is unlikely to have more than 5 people access the dabase at the same time.
my question is..... (access 2007.... can't see any user security).....
am i better to do an individual database (all exactly the same apart from name, titles etc) for each site that sits in their individual folders via terminal services? then let the head office access each seperate database via linked tables?
my thinking for the above way was security (they can't see each other) and resilience (1 database gets corrupted, the others are still workable) and speed (only a few users accessing each database)
the downsides are obviously the amount of files although this is nothing compared to the excel files at the moment.
any ideas or help would be appreciated.
thanks
louis
I am new to databases,, in fact I've never created one before,, However I started learning today , for this project,, I'm going to be learning on my own, but if someone feels kind they can give me the basics on how (the following) database would be created.
Fields
Box # (Number)
CBS Code (number)
Shackle Code (Number)
Agent Name
Property Street
Date Out
Date In
Where I can put in a Box # and it will fill in the other information for that box, showing if its currently out (aka no check in date with most current record),
If its not I can input Agent name, property, and Date out (then it will be checked out)? or some way to accomplish this.
Info can also be looked up by Agent name with autofill for the rest of the fields?,
Or Property with autofill
If searched by box Num, I want all previous agents used (check in and check out value) as well as current
If search by Agent, I want all box information from past, check in, and check in/out. with the rest of the records,,
I know this is possible,, And I'll figure it out,, But help speeding my learning would be appriciated
I've created a form that has a text field that concatenates values using: =![PREVOWN] & ![PARKNAME] & ![TRNSNUM] I know concatenation is a big no-no with normal forms, however, the agency I work for, wants to use this concatenated value as their unique identifier. Using a query alone is not feasible as the database was set up to use this value as the primary key, so we need to include the value.
I am having two problems:
The first is that the field [TRNSNUM], is an incremental autonumber field. The values in this field need to start at 10,000 so I used a Format of "10"000. This works great in the [TRNSNUM] field itself, but in the concatenated field, I am not seeing the first two digits, so my values for the last third of the concatenation are showing up 123, 124, ... instead of 10123, 10124. Any help on this issue? Also, is this just assigning the two digit field of 10 before my numbers? If so, I need a better way to do this, as my values will reach up to 11,000 and so on.
The next issue, and the larger problem is that my concatenated text field is not being input when I click on my forms, "submit" control button. I used the command button wizard to create this button. I selected "Record Operations" and then "Save record." It seems to save all of my other text field, except the concatenated field. This is a problem, since the value is supposed to be my primary key, so if I cannot obtain the value from my form, I cannot add more records, as the primary key cannot be null.
Any ideas would be of great help, I am a very beginning user, with very little knowledge of SQL or VB, so I am slogging my way through. Thanks.
I have spent the last couple of hours looking through the forums but have managed to get myself really confused. Lots of questions, sorry - if anyone can point me in the right direction with one or two of them, I would be hugely grateful!
I have a database in a shared folder on the network at work. It is due to start being used by users other than myself in future weeks, and I see here that it's sensible to split things into a FE and a BE.
1. My initial problem is that I have a nagging memory of being told that we're not supposed to save anything on the individual computers hard drives. Can a split leave both the BE and the various copies of the FE on the shared drive, or does this negate the reasons for splitting in the first place?
2. Additionally, with little space left on the shared drive itself, it's possible we'll not have the room to put 10+ copies of the FE on it anyway. If I just split the database into the BE and ONE copy of the FE which everyone accesses, will the BE at least be made more secure by the split, even if the FE is still vulnerable, and performance isn't improved?
3. If no sort of split is possible, are there any alternatives to splitting?
Lastly, some questions for if we ARE able to split and put the FE on each computer:
(I would be keeping the BE and the "master" version of the FE on the shared drive. All tables in the BE and queries, reports and forms for the "user interface" in the FE. Hopefully this is roughly what I'm supposed to do).
4. With a split database, what happens with compacting? Presumably the BE of the database can be compacted as normal, but what happens with all the versions of the FE? Does each user have to be responsible for compacting them individually? Does an FE even need compacting if the design is unchanged?
5. Does splitting affect what happens if two people either open or amend a record at the same time, or is that still just governed by the record locks setting in Tools (which would need to be set before splitting presumably)?
6. If the design of the FE changes (but none of the underlying tables, queries etc. are touched), do I just give each user a copy of the new version, or does something more complicated have to happen (ie. does the whole splitting process have to take place again)? What about if new tables are added to the BE, or if existing tables / queries are amended? Can I just issue amended versions of the FE that interacts with the new tables as needed?
7. I have drop down lists and combo boxs in the forms in the FE that use tables to populate them. Will it cause problems that these tables are in the BE - such as impractical amounts of time before combo boxs show their options? (The table has to be in the BE (I think?) because the user updates the content of these tables / combo boxs through one of the other forms in the FE)
Many, many, many thanks if anyone can help me out with any of these.
I'm a newbie to access and I need to build a database with the following specs:
Be able to click on a specific "skillset" button - 13 Skillsets in total.
This would take you to a list of people who are in that skillset
Then you would need to be able to click on a person and it would take you to the current day, with a list of about 5 stats. Eg. No. of calls taken, Duration of calls. (Data can then be entered / viewed)
How difficult would this be to do if it is even possible? Would anybody be kind enough to point me in the direction of a tutorial or give some advice.
I have a form for employees that contains their salaries. How would I go about creating a macro that would cause a warning box to display any time a salary greater than $100,000 is entered?
On the same employee form I have a textbox to input the email address. After you input the email I want a lostfocus event to check if it follows the same format as "username@email.com" If it wouldn't match that format an error message will be shown.
Recently I've been charged with the task of setting up a database for the company I work for. Not a big problem I thought, I sat down and started playing. I've figured out most all of what I wanted to do with one glaring exception.
I have 3 fields of data that I want added together and automatically spit out into a 4th field. If the 4th field was also unable to be altered by the user, that would be all the better.
For exampled I'm looking to add [Field1] [Field2] and [Field3] and have them spit out into the field [Output].
My coding knowledge is limited, and my access experience is almost as poor. Surely Access is capable of doing this, without having to build some elaborate script or macro or whatever it's called.
I'm trying to teach myself access. I'm having some difficulty, so let me explain my requirements.
I need a database that allows entry of reservations from multiple locations, and that can track how many of a particular item is being used.
Example: I have 32 devices, but only 7 paths that I can book to these devices. How can I set it up so that when someone attempts to book an 8th device, the system will alert them that there are no available slots for them to use. I would like to make the database available for editing for future expansion also.
I hope someone here won't tell me to take a hike, I know I'm a newbie, but once I dissect an idea, I catch on real quick.
First, if I have a table, and I want to combine two records together, how would I accomplish this? For example, say I have the following table.
NAME SALES Bill 2000 Jim 500 Ted 1000
And by some miracle of science, Bill and Jim are able to fuse together to become Jill, combining their sales together. So the new table would look ilke this.
NAME SALES Jill 2500 Ted 1000
My second question is, say I have an extended list of the same table with 30 names and corresponding sales. However, I want to find out Ted's percentage of sales of the TOP 15, not all 30. How can I accomplish this?
Thanks so much in advance for helping out a complete newbie. :)
Hi all, new member with a newbie question. I have just started working with Access, so pardon if this question is unclear or pitifully basic. We have a basic database comprised of client names, amounts paid to-date and balance due. It also has a field named date last paid, so here is the question. When you enter or change the amount in paid to-date, can you automatically have the date of the change (ie. Todays Date) entered in the "Date last paid" field.
I am trying to build a db for the office I work at. And I have a few questions about how I should design it.
1. My vendor number should be an autonumber, but it has a relationship to the vendor name on a report. I cant create a relationship btwn text and autonumber.
2. Right now I have 3 main tables. Vendor, builder, and job. All of this is for the marketing aspect of the business. But I want to include other aspects, like collections and payroll. Should I add columns to the jobs table, or create a new table? For example, on the jobs table, I already have job info like details on the job and who referred it to us. Should I add invoiced amount, amount collected, etc., or create a table called collections to store all of this info? Im really confused about this.
I'm having design questions for complicated business logic. I have 4 tables, Author, Plan, Analysis, and Report. 1 or many authors can write 0, 1 or many Plans, Analysis or Reports. And a Plan, Analysis and Report (all 3) can all have a 1 or many authors associated with it. So thats the first many-to-many. To make things worst, a Plan, Analysis, or Report can have 0, 1, or many Plan, Analysis, or Reports associated with it. In other words, a Plan be associated with a 0, 1 or many Analysis or Reports. An Analysis can be associated with 0, 1 or many Plans or Reports. A Report can be associated with 0, 1 or many Plans or Analysis. So I have 4 entities with all many-manys between them.
So by following traditional rules I would have 4 tables with 6 linking tables (author to plan, author to analysis, author to report, plan to analysis, analysis to report, plan to report). Is there a way to combine multiple linking tables into one or two linking tables shared between the 4 tables. Say one linking table between from author to plan, analysis and report and another linking table between plan, analysis and report? Any clarification would be greatly appreciated. If I had schema view like in access that would give me so much help. Thanks in advance!
I'm not sure how to proceed. Any help would be appreciated.
I'm in the military. I have a table with individuals who have been awarded various decorations. With each decoration a set of orders has to be published. If there is a mistake on the orders, another set of orders has to be published (amendments).
The original orders and subsequent amendments are sequentially numbered and dated. For example, GA001, 1 Jan 05; GA002, 3 Jan 05.
I have two tables: (1) demographic and award data; (2) Order numbers (with issue date; y/n field for amendment).
I linked the tables with a common field Table One (one) to Table Two (many).
I built a form for Table one. I want a subform in the master from to show all the orders issued for that person/decoration but I don't know how to show the next available order number without calling up Table two.
Is it possible to show all the assigned order#s/date PLUS the next available order number?
It has been a long while since I messed with Access (or any database) and I am having some trouble finding my way around in design and query.
I have a database with approximately 30 tables which are identical. (All imported from the same application.) The tables vary in size from 2 to 100 records. 6 fields in each. The database will be mainly used for retaining historical data. I would like to be able to query all of them at the same time for a keyword in a field for instance, but as well I think that there may be value in keeping them as individual tables.
I’m having trouble getting my head around the easiest way to tackle this simple project, and any guidance would be much appreciated.
Hi, New to forum. Beginner/Intermediate Access Experience.
Here is what I am trying to accomplish:
The database i am creating will basically need to have a user check-off a list of Fire Extinguishers that he has checked, on a monthly basis. For example, the user will enter a date, and then a form with a list of all the extinguishers will pop up, and he will place a check mark by each one. When he enters the check mark, that date will be stored so that we have a history of when each extinguisher was checked and by whom (using initials or something)
Currently, my design is simple, something like this:
My question is this: -Am I going down the right track with the 2 tables? -Do I need a third table to Store the Historical Data? -I am not quite sure how to layout the form so that all the extinguishers are listed.
I know these questions may seem vague, but any help would by highly appreciated. thank you.
I have attached a copy of an excel sheet that my company has been using since the ice age. I would like to set up an access db, so as to eliminate the multiple files and make it easier to create new orders, rather that saving as a new file name all the time. However, does anyone have an idea of how I could use this same format for order entry? I need to ease our sales staff into this new idea and don't really want to disturb the form they have been using forever. Thanks for any and all suggestions, Chad
In the right hand portion of my dialog I use a subform to display key words for the code. Is there a way to turn off the column and row headers for the form? Also is there a way to have a transparent background for the subform?
In order to add key words I need to open up another form, enter the words there, close that form and then select them from this dialog. It would be nice to be able to add keywords from this form. FYI, there is a many to many relationship between the key words and the code.
Is there a way to show just my dialog when the database is opened and not the rest of the Access application space? I have unchecked everything in the startup options already.
The last thing is I would love to be able to format the code section so that certain pieces of text are formatted. I am going to start messing around with the some of the free rich text controls but is there an out of the box solution that can handle this task? I was thinking about formatting the code in html and then displaying the html. Is this possible from within Access?
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.
I'm new to Access 2007 and only have a little knowledge from 1998? version some years ago. So please be gentle :-)
I've put together a database for entering the bookings for my taxi. (One man band) and would like some advice on adding a few bells and whistles.
1. Some of my bookings are daily/weekly/monthly and I would like to be able to add them all in one go. I am currently putting the booking into Excel and copying into the number of cells needed. I then change the dates using Fill and then import the lot into the DB.
2. On the main report page that shows all upcoming bookings I would like to differentiate between dates using different colours. Monday = Yellow Tuesday = Blue Wednesday = Yellow and so on.
3. I would like to show the DAY as well as the DATE. Example MONDAY 4th Feb 2008. The best I can get is 4th February 2008
4. I would also like to be able to choose an address from a dropdown menu but also be able to enter a new address manually if its not listed. This new address would be added to the list. I've tried using LOOKUP COLUMN but without any success.
I would include a copy of the DB but its full of customers personal data so I cant.
The DB is made up of the following Fields in a Table called Bookings. ID, Time, Date, Name, From, To, Notes, Account (Y/N), Paid (Y/N), Cost
I'm asking a lot I know. I can use the DB as is but I would like to add these items if possible.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
I have a sub form in DataSheet view and I would like to lock the design so that the User should not change the layout
Even when I set the property of the Datasheet “Allow Design Changes: Design View Only” users are able to unhide the hidden columns and they can also change the size of the column by dragging the column end line
Does any one know how to lock the design of datasheet ( I am using this sub form datasheet for data entry but do not want users to change the layout)
I'm going to make up names and values -- I'm interested in the structure.
Table ALPHA:
COLA DAT1 DAT2 DAT3 1 5 7 9 2 4 14 8
Table BETA:
COLA_IND DAT1 DAT2 DATN 1 a b c
Table CHARLIE:
COLA_IND DAT1 DAT2 2 d e
Table DELTA:
COLA_IND DAT1 DAT2 2 f g
Ok, the idea here is that the data in table ALPHA contains data with COLA a key such that selecting 2 would yield the data row "4, 14, 8."
Now, COLA_IND is a "COLA" key for table ALPHA (sorry, I can never remember which side is called the foreign key). So, from tables BETA, CHARLIE and DELTA, I can access any row in ALPHA based on the key "COLA_IND"
Here's the fun part. When I build my query, it wants to use an inner join on the keys from all these tables... In order words:
SELECT blah blah blah INNER JOIN blah ON (ALPHA.COLA=BETA.COLA_IND) AND (ALPHA.COLA=CHARLIE.COLA_IND) AND (ALPHA.COLA=DELTA.COLA_IND)
What I'm looking to do is expand BETA, CHARLIE and DELTA with the information from ALPHA based on the key COLA_IND. I don't think this is doing what I want.
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column (2) [shift+arrows] to select all of the columns I need (3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
1) There are some databases here at work that I can open in BOTH A97 and A2K. I thought A2K won't open in A97, and before you can open an A97 in A2K, you have to convert? At least that's been my experience thus far. Any idea what is happening and how I can determine which version these databases are currently in?
2) With those same databases...assuming some were A97, the option to Convert them under Database Utilities is greyed out. Is this most likely a rights issue? Could it be anything else?
Recently I created a database using MS Access for the company and I am the Admin for it. The database is in the share folder and many employees are using it but if I want to take backup or modify any thing a message is appearing saying that one user open the database.
Is there any method to know which user is opening the database and in which PC (workstation) it is open. --------------------------------------
Is there any way of adding export fuction to the toolbar using modules or macro
