Multi Value Tables
Oct 2, 2007
Hi Everyone,
I have read several tutorials on linking tables to store data, however I cant seem to get this right. I need a little help with my table design and structure. What I am trying to accomplish is a database that captures the following:
-Name
-Employee#
-Group # (1-25 One person can be in more than 1 group)
-Referred By (Only one name for each Group the person is in)
-Priority # (Can be a 1 or a 2 for each group # the person is in)
-Outcome of group (Just a small text explination of the outcome)
-Date Scheduled (Can be mutiple dates scheduled for each group)
So basically what I want to do is be able to retrieve a list of what Groups an emplyee is in, Who reffered them to that group, the Priority # of that person in the group and what dates they are scheduled for each group along with the outcome of each group they have attended. I know I need to join multiple tables but I am a bit stumped on how to go about doing this. Any help with my database and table design would be much appriciated. Thanks.
View Replies
ADVERTISEMENT
Jan 14, 2013
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
View 1 Replies
View Related
Sep 21, 2012
I am using collect data via email process to collect data by email, purpose is to update not to add new records but to update. The condition for update is to have data from one table only.
I have 4 tables data from which should be sent by email. These tables are related. I made a query based on tables and query is update-able. When I use this query, wizard does not give option of updating the data but only of adding new records.
I tried to first make a make table from a make table query but that too have the same result.
Any ways to make treat these tables in a single table? Almost all fields except one shall be just to read and one field shall be updated.
View 5 Replies
View Related
Feb 8, 2015
I have 4 fields in a table that when combined must be unique.I know you can setup multiple fields as a combined key, but I don't really need these to be the key (I will auto number a unique key). I just need to make sure the same combination of codes is not entered twice.Do I set this up as multiple key fields anyway?
View 1 Replies
View Related
Jan 5, 2005
(I know I posted this question before, but I fear it may have gotten lost in a thread so I am starting a new one)
I have created a multi-field pkey with the ModelDescription and VersionNumber fields. I did it the way that didn't require code as I could not find any online and am not versed enough in VB to do it on my own. I just highlighted both the fields and pressed the "Key" button.
My question is linking the tables. Should I just join them via the ModelDescription field? It won't let me join on both fields. Is it going to matter which field (ModelDescription vs. VersionNumber) I use to join the tables? I've never run into the need to have a multi-field pkey before.
View 1 Replies
View Related
Oct 20, 2013
So I have an event table with a muti value field with all the people on that event. This people field is look up column from my people table (so shows the name but stores the key).Anyhow what I now need to to do is record logs from the event for each person (from that muti value people field). I want ideally be prompted to enter the log data for each person, and show it related to the event it is for.
View 1 Replies
View Related
Nov 1, 2012
I have a field in which I wish to post several contacts. The contacts are listed in a table and populate the specific field via a drop down. In this specific field I want to select "mary" "bob" and "fred" (all three to display) however only one is displayed.....
View 3 Replies
View Related
Apr 12, 2013
Started a new thread: Rebuilt database and still working with auto number for multi user form. The code I have works for single user but not multi user. Also if someone opens the form and closes without adding anything to the form the number is used with blank record.
This is what I have thus far:
Private Sub Form_Current() Me.[PO] = Nz(DMax("[PO]", "tblPurchaseOrder"), 0) + 1
End Sub
View 5 Replies
View Related
Apr 27, 2013
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables?
2- how to cascade the updates to the related fields?
3- Is there any way to force the data update to other tables?
View 5 Replies
View Related
Sep 7, 2007
I have spent the last couple of days trying to figure out how to make this work.
I have three tables.
tblIntakeMain
[IntakeMainID]
tblIncidentDetails
[IncidentdeatailsID]
tblPersonnel
[PersonnelID]
On the main form I use subforms to link tblIncidentDetails and tblPersonnel to tblIntakeMain. Both subforms can, and do, have many entries. This all works fine. What is not working is the search form I am using.
I am using Gromits most excellent Search Form. The problem is when I create a query, qSearch, to bring together the three tables I get a multiple records which makes the searches very confusing and near useless. Is there anyway around this? Is there something I am missing? Is there another search method I could use that would work in a similar way as Gromits? Please help before the Prozac runs out and I lose my mind--what little it left.
View 5 Replies
View Related
Apr 10, 2008
Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.
At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)
I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.
I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.
I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.
Any advice on handling this issue, or do I basically just need to create a separate query for each table?
I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.
Thanks!
View 7 Replies
View Related
Feb 16, 2013
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
View 8 Replies
View Related
Nov 23, 2012
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
View 5 Replies
View Related
Aug 27, 2005
Now i need some help here.
I adapted this code from somewhere on this forum. But iam having a problem with using the 'ALL' criteria.
It Opens a Report Dynamically from multiple selections in the two multiselect ListBoxes, but when i select 'All' and select any other item in another listbox
For Example(All in Names and Home1 in Homes) it gives me all the Results.
I have tried several criterias to solve it out but iam still failing.
Hope there will be help. Attached is a sample db in A2K and A97.
Thanks in Advance.
View 3 Replies
View Related
Mar 2, 2007
I have read a lot about if multiple users want to share an MDB, they have to create BE and FE and split the database.
I understand having to do that if all those users want to write to the MDB simultaneously.
I have a situation where one person will do data entry (write access), and two other users will just want to view and perhaps just search the database (read).
If only one person is writing at any time, do I have to do all that splitting and FE/BE configuration?
View 2 Replies
View Related
Apr 17, 2007
I've create a form with subform that work like a charm, now they want in a multi user environment. What is the best way to achive this?
View 6 Replies
View Related
Dec 5, 2004
Hi everybody
I have an assesment and need to create a database, I'm just starting with access and I need some little help if anyone has time.
I'm doing the database for an interim managment company and i have problem with the experience category. Basically because every candidate has got at least 3 or 4 different experiences, I don't know how to design the table. All I know is that I probably will have to create a separate table for this.
Thanks to anyone who will help me.
Ilan
View 2 Replies
View Related
Jan 21, 2005
Is there a way to have multiple records under a record, such as top record 1 under it 1.1, 1.2 so on. then if you go back to the top record, new record 2 the records under it would be 2.1, 2.2. so on.
Thanks
View 5 Replies
View Related
Nov 7, 2006
Good Afternoon,
I need to be to create a Query that will show a 0 for no record.
My form is made up of a Name and a Course name.
e.g.:
Michael Underwriting
Scott Underwriting
Michael Claims
Michael IT
My End result needs to be
Name Underwriting Claims IT
Michael YES YES YES
Scott Yes 0 0
I have created 10 individual Queries filtering out each course
but then I join them together by Name it will only show Michael.
I hope its not a case of adding records for scott as I have 10,000 Names
Thank you
View 3 Replies
View Related
Jul 11, 2005
Hello,
Still trying to figure things out with this software. Never have messed with it much. The question that I have is this. We have about 9 forms that we will be filling out. I was originally going to have it to where you swapped between the forms in order to put the data in that we needed but was wondering if its possible to create 1 single form. Have a Mult Option box in place and depending on how that box is checked will depend on where the data is going to be stored. Is this possible? As I have said i'm new so forgive me for not knowing a whole lot.
Thanks
View 2 Replies
View Related
Jan 2, 2006
I am looking to see if anyone can help i have been able to get the multi select function part of my form working but i need another filter in the query for a date but it keeps deleting this part of the query everytime i run the report what i need to be able to do is get all jobs against a specific leadcraft that are in the past and also 31 days into the future.
Paul
here is the sql so far, thanks to those who helped already.
Private Sub preview_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("FAelecplan")
If Me!Leadcraftlist.ItemsSelected.Count > 0 Then
For Each varItem In Me!Leadcraftlist.ItemsSelected
strCriteria = strCriteria & "MAXIMO_V_WORKORDERS_FA.LEADCRAFT = " & Chr(34) _
& Me!Leadcraftlist.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "MAXIMO_V_WORKORDERS_FA.LEADCRAFT Like '*'"
End If
strSQL = "SELECT * FROM MAXIMO_V_WORKORDERS_FA " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
Dim stDocName As String
stDocName = "elecplan"
DoCmd.OpenReport stDocName, acPreview
On Error GoTo Err_preview_Click
Exit_preview_Click:
Exit Sub
Err_preview_Click:
MsgBox Err.Description
Resume Exit_preview_Click
End Sub
View 2 Replies
View Related
Apr 19, 2006
Hi there,
I'm sure I'm not being dim, but I really can't figure how to do this.
I have a list box with two columns. One column (bound) is a name, the other is a unique ID. I need to transfer both the Name and the ID to a second list box (again, two columns).
I can get it to do this with single selections, but not multiple ones, and this is what I need.
Any ideas?
Cheers
View 1 Replies
View Related
Sep 1, 2005
I plan to create a multi-user application on access 2000. There would be more than 20 users simultaneously logging in the database. The database would be kept in Server with high config. The users would work on the database (server) from their workstations. Forms/Queries/Reports ... are in the workstation (client). The database may growup to 70 MB every 3 months. The application will run on the database for 3 months which will be replaced with a new one after that period. Is it a good plan to do so on Access 2000. Am worried about the network traffic. Will the users face it as a very slow application ???
(SysConfig workstation: Win2K Pro/ 128 MB RAM/ 40GB HDD/Pentium-4)
Any suggestion is appreciated. Thanx in advance.
Prodigy.
View 2 Replies
View Related
Sep 5, 2005
Hi
If I want several users to access a database from their network pc's, should I place the access database .mdb file on the server and set the Sharing properties, then install the access application on each of their stations.
They need to each be able to use the database when they need to and read or add data, reports etc.
I am using access 2003.
Thanks
Dave
View 14 Replies
View Related
Mar 10, 2006
I maintain a database that is used by several people in my office. Only about 4 different people actually use the database but I would like the database to have multi-user support. As of right now the tables, forms, modules, etc. are all in a single mdb file. Can someone recommend a method for making this database accessible to several users at once? My main concerns are accuracy (changes commiting in the proper order) and low chance of corruption.
View 1 Replies
View Related
Oct 12, 2006
Hi all,
I have a form with three tabs.
Problem is that when user opens the form - it navigates to the first tab BUT navigates so far down that user cannot see the tab headings.
Although all tabs first item is as close to top as possible.
BTW - last two tabs are based on subforms.
Cheers
View 1 Replies
View Related
