Linking (1 Viewer)

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
I've got a question about MS Access

I've got table 1 with rows of items
I've got table 2 with 11 rows (and each row has 4 spots to place numbers in depending on the condition that the designated item has)

I'm wondering on how I can link each item (row) from table 1 to the 11 rows of table 2 and for each new row entered in table one... how could I make another 11 rows in table 2 with the information for the next item (so that the info added in from item 1 isnt deleted)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:22
Joined
Aug 30, 2003
Messages
36,118
Welcome to the site. I'm not sure I understand what you're trying to accomplish (and the lack of an answer already indicates others may also be confused). An append query or a recordset could be used to add records to table 2. Maybe a better description of the problem will help us help you.
 

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
thanks for the welcome :)

well what i'm trying to do is link an item to 11 other subcategories which i can get totals from

eg.

table 1
entry # | asset | total %(%1+%2+%3)
entry 1 | house | %Total

table 2

entry # | asset # | vul | %
entry 1 | 1 | chance of burning down | %1
entry 2 | 1 | chance of electric shock | %2
entry 3 | 1 | chance of flooding | %3

and for each additional entry to table 1, i'd make multiple entries with the same 'chance of' column but make a blank percentage column (so that the percentages for the old item is stored)

what i need help for is how would i create the additional rows with the same 'vuls' and blank %s for new assets

and how would i calculate the 3 different %s of asset #1

i started using MS access 2 days ago so please forgive my lack of knowledge in this field
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:22
Joined
Aug 30, 2003
Messages
36,118
Do you have a table with the 11 vul items in it (a lookup table). If so, it would be a fairly simple append query, fired off from the after insert event of the form used to enter the main value.
 

Mike375

Registered User.
Local time
Today, 19:22
Joined
Aug 28, 2008
Messages
2,548
thanks for the welcome :)

well what i'm trying to do is link an item to 11 other subcategories which i can get totals from

I am not sure I understand you but I suspect it might be a case of ID numbers in each table.

Basically Access works on the basis of one table having unique records, such as a Customer and each record has a unique ID number.

Other tables (or table) have records that are related to those in the first table such as Orders. In other words one Customer might have many Orders and thus several "related" records in the Order table. Each one of these related records has a field with an ID number that macthes the number for the Customer in the customer table.

This is used right throughout Access.

Table1 has Policy Holders and Table2 has their insurance benefit details.

Table1 has the doctor's patients and Table 2 has a record for each patient's visit.
 

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
yes i have a table with the 11 vuls on it
each vul is a row (making 11 rows of course)

i want it so that when the 'add record' pre-programmed button on my form is clicked, another 11 rows are placed under the first 11 with the same vuls, the asset number for all equaling to 2 and the percentages to be blank

would you be able to guide me through that process?




and thanks mike, im understanding it myself a bit more but i'm not sure what to do when a 'new patient' is added
like, what would i do with table 2 in the case of a new patient?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:22
Joined
Aug 30, 2003
Messages
36,118
I meant a separate table that would only contain the 11 valid options in it. Commonly in database design you'd have a lookup table that would contain 2 fields, a numeric field and a text description field. For you that would be let's say the numbers 1-11 and the 11 descriptions. In your table 2 you would store the numeric value rather than the full description. You'd use a query to pull them together for presentation to the user.

If you can post your db it may be simpler for me to show you.
 

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
Hmm...

Unfortunately I dont think it's allowed for me to post the db on the site.
However, i'm wondering if you'd be able to explain how i should be going about this problem i'm having if i think of it in your way



table 1 has a list of items and 4 columns (first 3 should be calculated in other tables in ms access by adding up values and the last one is an addition of the first 3)

table 1
entry # | asset | #1 | #2 | #3 | total %(#1+#2+#3)
entry 1 | house | ## | ## | ## | %Total

table 2 is the only one im worrying about right now
table 2 looks more like this:

ID Item Dimension 0 1 2 3 4
1 fgjhfdh 0 1 2 ...
2 fdjdgh 0 1 2 ...
3 sdfhfdh 0 1 2 ...
4 sujdfth 0 1 2...
5 sghddsfg 0 1 2...
6 hgkhjlg 0 1 2...
7 xcvbfgh 0 1 2...
8 tuiyu 0 1 2...
9 ertruds 0 1 2...
10 rertsf 0 1 2...
11 wteyr 0 1 2...

only 1 number per row (but it's the same number as the column header
then i have to add up the total of the columns (1 total for each column - 0, 1, 2, 3, 4) and then 1 total for the row of total values

Need to calculate but only have to show total value in table 1 for #1:
Totals of Cols 0 #x1 #x2 ...
Total value (add up all the 'totals of cols' together)

my second table is more of a small excel chart of numbers (11 x 5) but i must use access to complete my task

also only 1 number gets picked per row and the others are left blank for each row

(only bolded so you'd see this part better, no frustration at all :p) MAIN PROBLEM atm: i have to create a version of table 2 for every item added in table 1 and I'm just wondering if i could do that in any other way than a new database for each item added in.
 

Mike375

Registered User.
Local time
Today, 19:22
Joined
Aug 28, 2008
Messages
2,548
Hmm...

Unfortunately I dont think it's allowed for me to post the db on the site.

Just make a copy, extract the relevant parts and leave some records, Compact and Repair, send to Zip and attach the Zip. You will find it a 100 times easier to get your solution.
 

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
oh sorry it's not for that reason
it's not allowed for me to do that at my workplace

i know it'd be a lot easier but im just in a bad position right now
really sorry, but i can provide most of the info you need
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:22
Joined
Aug 30, 2003
Messages
36,118
I'm a little confused, as your new description of the 2 tables is different than before, and I don't see a field in table 2 that identifies the record from table 1. Can you post a screenshot of the 2 tables with data in them?
 

Mike375

Registered User.
Local time
Today, 19:22
Joined
Aug 28, 2008
Messages
2,548
only 1 number per row (but it's the same number as the column header
then i have to add up the total of the columns (1 total for each column - 0, 1, 2, 3, 4) and then 1 total for the row of total values

The easiest way to do that so it is similar to Excel is:

1) Make a continuous form based on the query.

2) In the footer put an unbound textbox for each field you want to add and in the textbox put =Sum([FieldName])

3) To get the total of the column additions add another unbound textbox and it place =[text1]+text2] etc with text1, text2 being the name of the unboumd text boxes you used to add the columns.
 

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
I'm a little confused, as your new description of the 2 tables is different than before, and I don't see a field in table 2 that identifies the record from table 1. Can you post a screenshot of the 2 tables with data in them?

sure thing paul
i actually made a mock db which is similar to what im working on

let me show you this






For each item i add in table 1, I want it to link with all the table 2 items
For table 2, the user of the program will be inputting values for the 0 to 5 section (one number per row matching the header)

I would like the program to calculate the totals of the columns 0 to 5 and then the grand total of the columns
and then display the grand total under Item Value (A) in table 1







 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:22
Joined
Aug 30, 2003
Messages
36,118
If you add a third table that only lists the 11 items, a simple append query can get the Item Number from the form that's adding the new record to the item table. Something like:

INSERT INTO [Mock Values](ID, [Item Number])
SELECT Forms!FormName.IDControlName, [Item Number]
FROM NewLookupTable

should add 11 records each with the ID from the form and each of the values in the new table. The spaces in your names are not a good idea by the way, as you then have to bracket them all the time.
 

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
If you add a third table that only lists the 11 items, a simple append query can get the Item Number from the form that's adding the new record to the item table. Something like:

INSERT INTO [Mock Values](ID, [Item Number])
SELECT Forms!FormName.IDControlName, [Item Number]
FROM NewLookupTable

should add 11 records each with the ID from the form and each of the values in the new table. The spaces in your names are not a good idea by the way, as you then have to bracket them all the time.


i'm just wondering if you would be able to explain your thought process and what the program does because I was told to start access programming 2 days ago and didn't want to just add whatever without knowing what it does

thanks for your time
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:22
Joined
Aug 30, 2003
Messages
36,118
Well, I'm trying to achieve your stated goal of adding 11 related records to table 2 when a record is added to table 1. I'm not sure I understand what the tables contain, so I'm withholding comment on that. There are a couple of things that concern me, so I'd simply say to search on "normalization" and make sure your tables comply.

Generally as developers we don't let users have direct access to tables and queries, just forms and reports. Thus my assumption that you will be adding records to table 1 through a form. So, I would use the after insert event of that form to execute that query. That query will use the ID from the form and the item numbers from the new table I recommended to add records to mock value. If you were on this form and added ID 2 to mock item, that query should add 11 records to mock value, each with ID 2 and item numbers 2-12. At that point you would have 2 records in mock item and 22 in mock value.
 

torontoguy

Registered User.
Local time
Today, 04:22
Joined
Jan 7, 2009
Messages
23
alright thanks

i believe you understood what i was trying to do so i'll try this now
however, i was wondering what the steps are for going through to make that query? Do i go to query wizard and click simple query? After that, which columns do i select? Thanks Paul (I know it's a little frustrating working with newbies).

btw: table 2 contains 1 number per row depending on importance and table 1 is just a total of all the numbers in table 2

for the third table and the form, it looks like:



 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:22
Joined
Aug 30, 2003
Messages
36,118
You can create the query 2 ways.

A)
  1. Query Design on the ribbon
  2. Close the Show Table dialog
  3. Click on SQL icon
  4. Paste in the SQL I posted above and tweak the various object names as appropriate

B)
  1. Query Design on the ribbon
  2. Add new lookup table
  3. Close the Show Table dialog
  4. Double click on field to be added
  5. Click on Append in the ribbon
  6. Select target table
  7. Make sure new "Append to" row in the grid is appropriate
  8. In a new column in the field row right click and select Build, and navigate to the form control that will contain the ID
  9. Select correct field in Append To row

See how far off that is.
 

Users who are viewing this thread

Top Bottom