HELP!! linking data from one table to another

Microsoft Access 2003 (full product)
July 8, 2010 at 13:52:56
Specs: Windows XP
I have a master table and i want to extract data and rearrange it in the new table. i set up the new table but now dont know what to do.

I have the following in my master table
item number/component

i want my new table to look like this

and i would like for it to be able to update itself somehow. Sometimes there are over 30 components so i made 50 fields incase the record stretches that long.

Please help ASAP

See More: HELP!! linking data from one table to another

Report •

July 8, 2010 at 14:58:47
Look at Responses 6 and 7 in this thread:

# 6 offers a Macro based solution.
# 7 does it with a formula.

Report •

July 9, 2010 at 06:02:48
Those solutions work for excel and I am using Access. BUT... I have my table in access linked to excel so i will just use the formula to link to the other worksheet/workbook.

I will let you know how it goes.

Thanks so much for the help!

Report •

July 9, 2010 at 10:42:02
Ok I was able to get it to work using the formula! And into Access. Now I need to create a report with the information BUT I want to hide labels and controls on the report if they are blank...any tips on how to do this? I have tried this:

CMP1=component 1 for the item
CMP1_Label=text on the report

Private Sub Details_Print(Cancel As Integer, PrintCount As Integer)

If Not Me.NewRecord Then
If IsNull(Me!CMP1) Then
Me!CMP1.Visible = False
Me!CMP1_Label.Visible = False
Me!CMP1.Visible = True
Me!CMP1_Label.Visible = True
End If
Me!CMP1.Visible = True
Me!CMP1_Label.Visible = True
End If

End Sub

Another question is how can I link this report to another report. I pasted it in the footer section and it doesnt work. I want each entry on a page to be by item number but that isn't the case.


Report •

Related Solutions

July 9, 2010 at 11:21:15

I am not sure, but testing CMP1 for Null is probably not what you want.

I am not sure what values CMP1 is expected to take,
so possible options might be:
if CMP1=""
if CMP1=0
if CMP1.Value=""
if CMP1.Text=""

You could try putting a break point at:
If IsNull(Me!CMP1) Then
and adding a watch for CMP1
then examine what value CMP1 has, and what parameters it takes.


Report •

July 9, 2010 at 12:20:06
I'm not sure what you mean by break point?

I will try your suggestions.


The code isn't working still. I have this...

Private Sub Details_Print(Cancel As Integer, PrintCount As Integer)

If (CMP75) = "" Then
Me!CMP75.Visible = False
Me!CMP75_Label.Visible = False
Me!CMP75.Visible = True
Me!CMP75_Label.Visible = True
End If

End Sub

Report •

July 9, 2010 at 13:18:06

A breakpoint will cause the code to stop running and you can then test some of the values etc.

Then you can use the f8 key to move forward one line of code at a time.

To create a breakpoint move the cursor into the area to the immediate left of the line of code.
Click in this area and a round marker appears and the line of code is highlighted.

When the macro runs, it will stop at that line (the line is then highlighted in yellow).

If you need to know about an object or a variable, select it and right-click and select 'Add Watch...' and OK from the dialog box. The selected item can be viewed in a new window that opens.

I can't test any of this for you as I don't know what the items are = what is CMP1 and where is it - is it in a user form and is this in Access or Excel.


Report •

Ask Question