I only have one table and I read so often that its better to have multiple tables. For what I have, it seems to work fine.
Will this cause problems down the road?
Thank you once again
John D
Hi,
I wonder if someone can help with what must be an easy solution to this problem.
I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.
Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.
I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.
Thanks,
Adam.
Please be patient with me.. really a beginner here. Thanks. :)
Here is my question:
I have multiple tableS (and by multiple, I mean a LOT of tables.) They are all similarly designed, with mailing addresses of potential customers. They were all entered at different locations and different timing. What I want to do is to take out all customers from a particular country at one go. The simple and tedious way is of course to merge ALL the tables then run a query. But is there a simpler way?
Somebody wrote me a SQL thingy, that helps me remove all the duplicates, so that is not a problem. But the merging of the tables is too tedious considering the number of tables involved. I myself have around zero knowledge of SQL, but if the solution is in that direction, please let me know, and I will find someone who can help figure it out.
THANKS!
I have an input form that has many fields, how do I go about fragmenting the data collection.
I thought I might group related data fileds together ie personal details, contact details and have a separate form for each data grouping or one form and make the fields invisible after they are filled in and then make the new data group fileds visible ie overlaying them so it appears to be a new form. I am not sure and would appreciate guidance.
regards
Peter
I'm trying to find the best way to organize a report that will have ALOT of fields for an individual record. Because it is for an individual record not a group of records (and I want it to be dynamic) I'm thinking of doing a filter & query to fill in report with necessary details. This report is going to contain ALOT of fields, even the wizard that I used based on the query said that there were too many fields, but I can't cut down.
View 4 Replies View RelatedI created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
In this database, there is a table of users (name, address, phone, etc.) There is also a table of 35 groups. Each user may be in any combination of 1 or all of the groups. The list of groups is not static. At any point in time, I need to be able to show that Bob is in groups 1, 19 and 35. All I can think to do is list all 35 groups in the user table, then make each one a yes or no field. HELP! There’s got to be a better, more efficient way.
View 3 Replies View RelatedAll,
I have a search form (continuous) which filters results from the main table. I can then double click on one of the filtered results to open a new form (single) showing all the fields for that entry. Fine so far.
My question - There are ten different types of operation that can be displayed, all with slightly different relevant fields.
Which of these methods is more efficient:
1. Make ten custom made forms (the correct one displayed depending on the selected operation). - Easy to do but needs ten forms.
or
2. Have one form with all the fields added (only the correct fields made visible by code). - Complicated code.
What do you think ?
I have been working on a Query to sort through a list of companies. I am trying to query the company name to include ALL entries that have the include key(from another table) anywhere in their company name... It is supposed to exclude all entries with ONLY the exclude key(from another table)... If it has the exclude key AND the include key or neither key then the entry should be included... Then it is supposed to use all of the entries added from the previous sorts and include ONLY the entries where the zipcode matches one from a ZIPCode list.
Below I have listed the tables with their variables and my attempt on the query:
Include Table (tblinc)
ID
Inckey
Exclude Table (tblex)
ID
exkey
List Table (list)
ID
Company
Address
City
State
ZIPCode
…
ZIPCode Table (ZIPCode)
ID
ZIPCode
Query withZips <--- my attempt... doesnt work 100% ALOT OF REPETITIVE ENTRIES
SELECT DISTINCT tblinc.inckey, tblex.exkey, ZIPCode.ZIPCode, *
FROM ((list LEFT JOIN tblinc ON list.company Like "* " & tblinc.inckey & " *") LEFT JOIN tblex ON list.company Like "* " & tblex.exkey & " *") INNER JOIN ZIPCode ON list.ZipCode Like "*" & ZIPCode.ZIPCode & "*"
ORDER BY inckey DESC , exkey;
This has become urgent... If you can help, please respond asap...
Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.
I manage an EMR from which a datapull occurs on discharge to various access databases.
They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.
tblTriageVisits and tblTriageProcedures
tblTriageVisits has the following fields
-Patient ID
-Export Time
[Code].....
The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits
It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.
The pulls work great and the duplicate record elimination method works great.
I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.
I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.
So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge
I used the following two formulas (the first one on the procedure name field the second on the procedure date field)
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))
And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.
Did I approach this problem correctly? And if so, how do I get rid of those spaces?
Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
Hi there :)
I'm not sure if I've picked the right forum topic for this... It's not a straightforward table question, and I think the only way I can do what I need is with a query of some sort. Apologies if it should have gone in 'Tables'...
The situation is: I'm creating a database (using Access 2003) in which our secretaries can enter exam marks for students. The difficulty is that there are ten papers (exams), each student's exam is marked by at least two examiners and there are at least 6 examiners per paper. Every examiner also marks more than one paper, but doesn't mark all papers.
So, I've got a table with all of the examiners and the papers they're marking and I've got a table with all the students and the papers they're taking. Because we have to enter the marks in such a way that we know both which mark an examiner gave and (obviously) which mark a student received (e.g. so we can look at statistics for each examiner), I thought the best way to do this would be to run a query that created a new table per paper in which the field names would be: Student_ID, <name of examiner 1 for the paper>, <name of examiner 2 for the paper>, <name of examiner 3 for the paper>, etc until the end of the list of examiners for that paper. The student's number would populate the Student_ID field and then the secretaries would enter each examiner's mark in their named field. Like this:
| Student_ID | A.N. Other | J. Blogs | J.Q. Public | Agreed_Mark |
1234A 72 74 73
2345B 65 68 67
3456C 71 73 72
4567D 52 51 52
Listing the name of every examiner (including those who aren't marking that particular paper) isn't an option (and would have to be hard coded into a table in any case, as far as I'm aware, either in a query or in a table design. This is something I'm loathe to do). Ideally, the secretary would be able to press a button on a form and have all the mark entry tables for all the papers created automagically.
The examiners for the different papers change every year, and not every academic marks exams every year, so this data will not remain static. Hard coding a table with examiner names would make more work for the secretaries. (I want to avoid this as all mark entering for all students is done in a single day. This means over 600 students at 3 marks per student [mark 1, mark 2, and final agreed mark] with time to contact examiners with questions... and only 2 secretaries, each of whom deals with a different section of students).
Because of the nature of the marking, it would also make quite a lot of work for the secretaries if we had to cross reference a generic Examiner_1 field with a different examiner for each paper, so the field name really must be the examiner's actual name.
Can anyone help me? I'm happy to do it with either VB or SQL; I'm not a programmer, but I can usually work out what code does even if I can't write it myself. I should be able to adapt anything presented as long as it's complete and doesn't assume that I know the basics. :) I've also got two reference books to hand: _Microsoft Access 2003 Forms, Reports and Queries_ by Paul McFedries and _How to Do Everything with Microsoft Office Access 2003_ by Virginia Anderson.
Thanks very much for your time. :)
-Jen
In Access 2010, in a table or form, are you able to use data from a lookup table like you can in a vlookup in Excel?
I am creating a Student database starting with the MS Students template. I will have a Parameter Table that contains weighting factors for each test for a given class. When scores for a particular test are entered for each student in a Test Results table, I would like to use the weighting factors for that test from the Parameter Table. These would be used in formulas/expressions to calculate a weighted score for the particular test as well as class-to-date average for all the tests to that point.
It seems this can be done in a query, but I am wondering if it can also be done in form so that when the instructor inputs the test scores the calculations are performed and the results of the calculations displayed. Or, if not, can the query be "built in" to the form to accomplish the same effect?
Hello
I have a table with 3 fields:
- ID no (primany key), Time1 and Time2
I have 3 records with ID no 1 to 3.
So my table look like this
ID no: Time1: Time2:
1 [emty] [emty]
2 [emty] [emty]
3 [emty] [emty]
I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
I have a command button that I want to automatically enter the date into a date field on the same form.
Setvalue
item: [Forms]![FRM_View_Orders]![OrderDate]
expression: Date()
This works in Access 2000 -- but in Access 2003 I get this error message:
"Object or class does not support the set of events"
Please can anyone help me with what I am doing wrong in 2003.
Thanks!
I am trying to populate fields from one table into another field in a linked table. Specifically, I have a giving table and a persons table. I have the giving table have the persons primary ID field in the giving table. I cannot seem to automatically link them if I am adding a "gift" record. The gift is a dollar amount given by that person. I have a unique primary key for each financial contribution which is supposed to be tied to a person's primary key and last name in my "person" table. Am I missing something in this design?
View 3 Replies View RelatedHi everyone,
I was wondering if this is possible to do:
I have a Form that reads from one Linked Table and Updates a Local Table. Is it possible to move the Local Table to the Back End and still be able to append fields and change field names etc?
If anyone can help, that would be greatly appreciated. Thank you in advance.
Bear
Hi Guys,
Haven't done anything with Access since I finished uni and I can't remember much. I'm trying to set up a simple database with customer recording and order taking functionality. Just in the very early stages trying to get basic things working.
I have a form where you enter customer details..at the bottom there is a command button named "Place Order", which will be clicked to go to the order form for that customer. Attachement shows very simplified version of the order form. I need to enter a Product ID, and have the Product Name automatically extracted from the Products table.
How can I do this? I would have done a search...but not even sure what this process is called in Access.
Thanks for any help.
I have a form with a text box from a table (cus. ID) and when I input that cus.ID into that text box I would like for that data to be stored in the appropriate table (customer) and also in the bridge table (customer purchases). Is there any way to input the data once into a textbox on the form and have it recorded into two different tables? Please let me know. Thanks
View 1 Replies View RelatedI have a database with a number of tables 2 of which are:
# Personal Members Table (with all their details and addresses and contact details)
# Organisation Table (including who are members of this Org)
My conundrum is this; In the Org table I have just created a 'Treasurer' Field and want to choose a name from the Members Table so that I do not have to put the data in twice (which is what has happened in the past.)
I have linked the tables and but within the Members table the Field names for the Personal Name are as follows <FirstName> <LastName> and there is another for the <Org> they are connected with...
In the Members table it would also be good to have the ability to hover the mouse over the <org> field (or click) on it and then underneath a box appears with everyone who is a member of that org. I have seen this working on another database but cannot seem to replicate it here for some reason?
I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.
I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.
This database is for quality control checking purposes.
First off,
Database 1:
Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.
Database 2:
This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.
What basically ends up happening is that the data in Database1 become Table fields in database2.
Example:
Database1:
Radio Check Audit Standard.
Needs to check following:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Another Random Check Audit Standard
Needs to check the following
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Did something specific for this Audit
Didn't do something else, also specific for this Audit.
Etc.
Continue on with other Audits.
Database2: Will keep track of all the quality checks that have been preformed.
Today we will check Radio Protocol
Did they:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Yes, No as necessary.
2 Days later, we need to do another QC on the Radio Protocol
Need to check if they did:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.
Etc.
But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.
Right now, i see 2 options:
Option1
Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc.
The fields in Database1 are automatically put into this specific instance of Quality Check, etc.
Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.
Option2:
When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.
Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.
I'm seen the first option as the only viable one.
Am I missing this, is there some other way to do this that would be better / wiser? Etc?
Kelemit
Hello,
Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet.
They have a common row ie say account number and other common fields that need to be updated.
thanks
I am trying to create the form so when the first field (Start Date) has a date selected, it limits the following field (Report Date) to the matching available data, and same with the third field (Production Division) on the previous two.
I also need the form to allow new dates / production division combinations, but not duplicate.
I starting working with a cascade function but got confused and not sure if that is where I should be headed.
I have attached 2 different formats of the same sample data.
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies View RelatedI have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub
I'm trying to build a form based on a table where the user can also enter data to update the table but with data fields not originally in the table. Below is a summary of what I have and what I need:
1.) A file that will be uploaded daily into a Table (ie name Denials extract table).
2.) A form bound to table Denials extract
3.) Additional fields that will be updated by users that is not included in this table b/c they are not available in the system we are getting the data extract from.
4.) The user will look up a key field that will allow them to review it in our system. Once they "work" the record, there will be multiple fields that they will update with this information that are not in the bound recordsource (table Denials extract).
Do I create another table with these fields even though they will be blank at first and make them a subform that will have a "primary key field" that will link the main form with the subform? Will they need to "update" a whole new table? Not really sure how that would work since they will be updating fields not in the bound table.