Data Entry And Relationships
Jan 31, 2006
Hi everyone,
I have a relationship between Market data table and Trade table which is One to Many. Market on the One and Trade on the Many.
However I just found out that I am unable to enter information into the Trade Form without entering information into the Market Form first.
I would like to enter info. into Trade Form/tables independent of whether I enter info. into the Market Form/table, but I still need to have the relationship of One Market data to Many Trade date.
Thanks
dfuas
View Replies
ADVERTISEMENT
Mar 19, 2008
You know the scenario where people keep working in an old version of a database, while you work on an update... leaving you with a new database, but old data?
This concerns a database, containing about 20-25 tables all linked together.
I was asked to make an update. In this update 3 new tables were included and here and there a few of the old tables got news fields, nothing crucial and a lot of forms were changed.
The current situation:
Database 1: contains new data, misses a few tables, is missing some fields, forms and queries
Database 1.1: contains old data, but some new extra tables, added fields, forms and queries
I would like to know if it is possible to import all data from 'database 1' into 'database 1.1', so that all linked information between the data is preserved, all database relations are maintained.
If I import tables from Database 1 into database 1.1, tables just get added, not replaced. If I delete or rename tables, I loose my relationships.
I would like to know what is a common and effective way to get all the new data of an old version of a database (where people keep working in while you work on an update), into the new version of the database, that currently contains old data.
View 3 Replies
View Related
Mar 9, 2006
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
View 2 Replies
View Related
Mar 5, 2015
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
View 1 Replies
View Related
Feb 9, 2015
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
View 3 Replies
View Related
Dec 18, 2014
I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.
View 5 Replies
View Related
Feb 18, 2015
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
View 5 Replies
View Related
Jan 5, 2013
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
View 4 Replies
View Related
Sep 28, 2014
My db has multiple linked tables. As time passes and work on a project continues data needs to be added to a separate table that is linked to the first table to be populated.
i.e., Initial design data is entered first, when drilling is complete more data is added to a linked table, when drilling is surveyed yet more data is added to the second table.
What I want to be able to do is to query for a particular set of existing design data, display this on a form and on the same line enable data entry to other fields from other tables which are linked to the existing design data.
View 7 Replies
View Related
Aug 20, 2007
I have 2 excel sheets that I have imported as external data. I created a relationship between the 2 tables on part# field that is common to both. The relationship is to take all info from A table and only those from B table that match. The part# field is set to text in both tables. When I run a report that merges the 2 together a significant # of parts in my field change to a negative number. For instance the part# in both tables are 12345678 it is changed to a random -3452345 number..
Any ideas?
Please help
View 1 Replies
View Related
Mar 5, 2008
I have a shared db where there is a requirement for more than 1 user to enter data in the same form and hence the same table. Up till now there has only been 1 data entry clerk so there has not been a problem.
Would accessing the same form by different users cause a problem, assumming they are inputting different records?
View 9 Replies
View Related
Jul 6, 2005
This has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!
View 3 Replies
View Related
May 31, 2005
Hi, I want to create an access app that will allow a user to see the number of pages printed by various printers.
These are the data that I have:
Model Type (various are available)
Printer's name (unique to one printer)
IP addr (unique to printer)
Quantity printed (such as Single Legal, Double Legal, Single Letter,etc)
This is what I have (2 tabels with 1-many relationship):
Printers
PrinterID
Model
Name
IP
ModelTypes
ModelID
Model
Now, where do I place the quanties of paper printed? Do I just leave it in Printers?
Thanks.
View 9 Replies
View Related
Nov 22, 2006
Hi All
I've worked with Access in the past but haven't touched it for 6 years now so I've forgot a lot of the things i've learnt. Bare with me while I try and explain it as clearly as possible
I'm creating a Database for work which will register all our Commerical Clients, which projects the clients are related to and which students worked on them projects. So I have 3 main Tables:
1.Commercial Clients
Client ID
Company Name
Contact
Title
2. Commercial Projects
Project ID
Nature of Project (Graphic Design, Multimedia, Media)
Project Name
Project Information
Staff Member Responsible
Start Date
End Date
3. Students/Graduates
Student ID
Student Name
Area of Expertise
Contact Details
Last Project worked on
Right here we go.....
Each commercial Client can have more then one project to their name. Each Project can have more than one student working on it and each student can be working on more then one project.
My relationships are set up like this:
Commercial Clients <One to Many> Projects
Projects <Many to Many>Students
What I have done is create forms with sub forms in to enter to extra data. I dont know if this is the right way to do it but it seems to easiest way. Please show me a way that will work better if you know it.
Anyway... the way the tables are now I can on cascade the information down through the tables.... it will not cascade upwards. I have ticked everything you can possibly tick for all relationships. So when I can enter projects in the Subform on the Commerical Clients Form and I can enter students on the subform in the Projects form. But on the Student form the projects the students at on does not show up in that subform and entering the day only tries duplicating it (which i dont want to do). If I had a form that showed the projects and the related commercial clients in the subform it wont find the commerical clients. So i.e.
It shows fields when the cascaded down like:
Commercial Clients
V
V
Projects
V
V
Students/Graduates
But it wont show if its:
Commercial Clients
^
^
Projects
^
^
Students/Graduates
Anyone have any ideas how I get get them to cascade upwards?
Thanks
Pete
View 9 Replies
View Related
Aug 15, 2006
K,
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,
View 6 Replies
View Related
Aug 26, 2014
My problem is displaying data in many-to-many relationships.
There are three main tables: tblShops, tblOwners and tblMarketingEvents.
These are linked through two junction tables creating two many-to-many relationships:
1) Each Shop may have more than one Owner and each Owner may have more than one Shop.
2) Each Marketing Event may reach more than one Owner and each Owner may be reached by more than one Event.
Now I want to display for each Marketing Event:
1) the data of that event, (tblMarketingEvents)
2) plus the owners reached in that event, (tblOwners)
3) plus all the shops owned by the owners reached in that event. (tblShops)
Preferably, I want to display more than one record at a time without repeating any information.
Is there a way to display the data like this?
I tried a query: it gives the right data but does not display it as I need. It repeats the data from tblMarketingEvents and tblOwners for each shop.
I tried a form with subform: it can only display one record at a time and is hard to work with (printing etc)
I tried a report with grouping levels based on the query: Again, right information, wrong display. I need to group according to the records of the MarketingEvents table and not just according to a single field.
View 3 Replies
View Related
Jun 4, 2013
I have created a form with two subforms. The context of this is a litigation database. The main form is for testimony. Among other things, it includes a field for a question and an answer. Any given piece of testimony may relate to one or more documents, and any document may be referenced in one or more pieces of testimony. So have two subforms, one for documents produced by the Plaintiff, and one for documents produced by the Defendant. It worked out easier that way because I originally created indices of those documents in separate Excel tables which I then imported to Access.
So the relationship for both document tables relative to the testimony table is many to many, which is established using two junction tables.
I also have a combo box in each subform where I can autofill fields based on selection of the document number.
My problem is that while the combo-box works great, once I select a document in the subform, that selection is carried over into the next entry of the master form, but also the selection is not retained in any of the master forms. So I can browse the document listing from any entry in the master form, but I can't get the document selection in the subform to stick and create a lasting relationship to a given record in the master form.
Is my problem that it is just impossible to have a subform based on a many to many relationship, or is it in the parent child relationship of the form and subform, or is it in the properties of the subform?
View 13 Replies
View Related
Aug 5, 2013
I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.
tblLangInfo: LangID (PK), language name, language family, etc.
tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in
tblSegmentLangJoin: LangID (PK), SegmentID(PK)
I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:
tblProcesses: ProcessID (PK), Process name
tblProcessLangJoin: LangID (PK), ProcessID (PK)
Here's the fun part...
I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:
Process1_triggers
Process1_targets
Process2_triggers
...
This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.
The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.
E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.
I've attached a picture to illustrate the relationship I need, since that's likely clearer.
One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.
View 3 Replies
View Related
Feb 19, 2014
I have a DB setup with users and their information. How can I remove a user from the DB without losing their data?
Basically I just don't want the users seeing this (deleted) user in the list on a form. So maybe "hide" is a better term than delete.
Details:
Access 2010
Table "Employees" - holds username and some other user specific info.
Table "Hours" - holds username related data(how many hours they worked on specific dates).
Form "WorkHourEntry" - The form I use to allow the users to login and enter their data(hours).
Then have a combo box that displays all the users in a list. This list/combo box is where I want to hide users from when the employee leaves the company or dept. and is no longer needed. But, we need to keep their history/data.
View 5 Replies
View Related
Jul 21, 2007
I'm attempting to normalize an enormous table with order data, but I'm running into some problems. The table currently contains many duplicates, of which also included the actual order information (yikes!), but I managed to normalize it almost all the way down. It appears that different accounts can be used on orders, and these order numbers are being recycled for some reason months down the line (don't ask my why they're reusing them for future orders because I have no idea either, they should be creating new order numbers). Of course, the Order number is the primary key in my table as it should be. I guess the same thing can occur with the sales rep. Anyway, I'm struggling to find the "best practice way" to deal with this situation. I'm almost tempted to create an intermediary "transaction table" or something like that between the main general order information (which at this point will basically be the Order Number and Customer ID only), then include a table with the account information and sales rep info, then have that link to the Order Detail with the products, quantity, order number and various dates for those order numbers. Order maybe it should be a separate, related table, but not between the general order information and the order details? Can anyone tell me if I'm on the right track for this situation? It was a total curveball that the rep and account information could be different on these orders.
Option 1:
Order (Order #, Customer) -> Transaction Information (Order #, Account Type, Sales Person) -> Order Details (dates, products, quantities, etc)
Option 2:
Order (Order #, Customer)---> Transaction Information (Order #, Account
| Type, Sales Person)
|
|-> Order Details (dates, products, quantities, etc)
View 2 Replies
View Related
May 3, 2013
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
View 14 Replies
View Related
Apr 19, 2006
How do I make data entry required, I've sent it to be in the table but it doesnt seem to work in the formI've tried the below with no luck Private Sub Command14_Click() If tbName = Null Or tbTelephone = Null Or tbAddress = Null Then MsgBox "Please complete all details.", vbExclamation Else DoCmd.Close DoCmd.OpenForm "frmInvoice" DoCmd.MoveSize , , 5100End IfEnd SubCheers
View 1 Replies
View Related
Jul 18, 2006
Hello,
I'm looking for opinions on how people like to enter information into a many to many relationship.I understand how a many to many works as well as a Main Form/Sub Form.
Subforms is what I currently use. Would you do anything different? Below is a sample of fields I currently have. Any advice? I keep wondering if there is a better way. I'm not a huge fan of sub forms. I also build lookups from the Junction. Is this correct?
tblProjects
ProjectID PK
Project
Date
Budget
tblProjectsandManagers
ProjectID PK FK
ManagerID PK FK
tblManagers
ManagerID PK
FirstName
LastName
Phone#
Thanks
View 1 Replies
View Related
May 18, 2005
I've got two tables, Properties and Valuations. Every six months the properties are revalued. I want to have a datasheet list of all the properties with a blank field for the new valuation so that the user can just go down the list and enter the new amount without having to select every property individually. Can I do this?
View 5 Replies
View Related
Aug 3, 2006
I want to have my records in my form show the latest entry, not a completely new form. The user will click on the new record button to create a new record but I want the user to be able to see the last record. Anybody know how to do this?
View 14 Replies
View Related
Jan 14, 2008
Sales information can be added against a customer record. What I would like to achieve is for users to still be able to record new sales information, but historical sales information (e.g. before 2007) can be viewed but not be able to be edited or changed. Is there a way of achieving this.
Thanks in advance...Paul
View 3 Replies
View Related
