Lookup Table Used More than Once in a Query

[Guest]

This is an example of what I need to do. (The data is from an outside source
so I am stuck with format)

lookup
id | value
-------------
1 | Yes
2 | No

Data
User | Q1| Q2 | Q3|
U1 | 2 | 1 | 2 |
U2 | 1 | 1 | 1 |

I need it to display

U1 No | Yes | No
U2 Yes| Yes | Yes

I have tried using multiple instances of lookup

Lookup--data.Q1, Lookup_1--data.Q2 Lookup_3--data.Q3

but I get an error (...Opened Exclusively...) And this is with a dummy
database with just those tables (closed) and this query.

here is the SQL

SELECT lookup.value, lookup_1.value, lookup_2.value, Data.User
FROM lookup AS lookup_2 INNER JOIN (lookup INNER JOIN (lookup AS lookup_1
INNER JOIN Data ON lookup_1.id = Data.Q2) ON lookup.id = Data.Q1) ON
lookup_2.id = Data.Q3;

Any Ideas?

Thanks

 

[Guest]

Both 'value' and 'user' are reserved words. That can lead to errors. You
might try putting square brackets [ ] around the field names or changing the
field names to something not reserved.

Check out the following for reserved words and some of the problems they can
cause:
http://support.microsoft.com/kb/286335/

I'd be tempted to normalize the data using Union queries then running a
crosstab on it. In the long run, this would be best.

You could also just forget about the Lookup table and use IIf statements IF
1 and 2 were the only values.

Q1s: IIf([Q1] = 1, "Yes","No")
Q2s: IIf([Q2] = 1, "Yes","No")
Q3s: IIf([Q3] = 1, "Yes","No")

 

[Guest]

I have Changed the names, but that is not the root of the problem, this is a
much simplified version of my problem.

The actual table has several instances of codes (in some cases 20) and each
lookup table has 20 values in it... IE

ProblemCatagory, ProblemCatagory1,...,ProblemCatagory16
All share values in ProblemCatagoryLookup (which has 20 Values in it).
This is repeated several times in the data, so you can see why I don't want
to have ifs, or make more than one lookup table.

The data is imported from a fixed column text file provided from an outside
source.

Also fyi the table being locked is the lookup table.

I have read that the multiple instances of the table should work, think it
may be a setting in record locking options?

Thanks.

Both 'value' and 'user' are reserved words. That can lead to errors. You
might try putting square brackets [ ] around the field names or changing the
field names to something not reserved.

Check out the following for reserved words and some of the problems they can
cause:
http://support.microsoft.com/kb/286335/

I'd be tempted to normalize the data using Union queries then running a
crosstab on it. In the long run, this would be best.

You could also just forget about the Lookup table and use IIf statements IF
1 and 2 were the only values.

Q1s: IIf([Q1] = 1, "Yes","No")
Q2s: IIf([Q2] = 1, "Yes","No")
Q3s: IIf([Q3] = 1, "Yes","No")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

This is an example of what I need to do. (The data is from an outside source
so I am stuck with format)

lookup
id | value
-------------
1 | Yes
2 | No

Data
User | Q1| Q2 | Q3|
U1 | 2 | 1 | 2 |
U2 | 1 | 1 | 1 |

I need it to display

U1 No | Yes | No
U2 Yes| Yes | Yes

I have tried using multiple instances of lookup

Lookup--data.Q1, Lookup_1--data.Q2 Lookup_3--data.Q3

but I get an error (...Opened Exclusively...) And this is with a dummy
database with just those tables (closed) and this query.

here is the SQL

SELECT lookup.value, lookup_1.value, lookup_2.value, Data.User
FROM lookup AS lookup_2 INNER JOIN (lookup INNER JOIN (lookup AS lookup_1
INNER JOIN Data ON lookup_1.id = Data.Q2) ON lookup.id = Data.Q1) ON
lookup_2.id = Data.Q3;

Any Ideas?

Thanks

 

[Guest]

The data will not be edited anyhow so I changed Query Data type to Snapshot,
This seems to solve the problem. Thanks!

I have Changed the names, but that is not the root of the problem, this is a
much simplified version of my problem.

The actual table has several instances of codes (in some cases 20) and each
lookup table has 20 values in it... IE

ProblemCatagory, ProblemCatagory1,...,ProblemCatagory16
All share values in ProblemCatagoryLookup (which has 20 Values in it).
This is repeated several times in the data, so you can see why I don't want
to have ifs, or make more than one lookup table.

The data is imported from a fixed column text file provided from an outside
source.

Also fyi the table being locked is the lookup table.

I have read that the multiple instances of the table should work, think it
may be a setting in record locking options?

Thanks.

Both 'value' and 'user' are reserved words. That can lead to errors. You
might try putting square brackets [ ] around the field names or changing the
field names to something not reserved.

Check out the following for reserved words and some of the problems they can
cause:
http://support.microsoft.com/kb/286335/

I'd be tempted to normalize the data using Union queries then running a
crosstab on it. In the long run, this would be best.

You could also just forget about the Lookup table and use IIf statements IF
1 and 2 were the only values.

Q1s: IIf([Q1] = 1, "Yes","No")
Q2s: IIf([Q2] = 1, "Yes","No")
Q3s: IIf([Q3] = 1, "Yes","No")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

This is an example of what I need to do. (The data is from an outside source
so I am stuck with format)

lookup
id | value
-------------
1 | Yes
2 | No

Data
User | Q1| Q2 | Q3|
U1 | 2 | 1 | 2 |
U2 | 1 | 1 | 1 |

I need it to display

U1 No | Yes | No
U2 Yes| Yes | Yes

I have tried using multiple instances of lookup

Lookup--data.Q1, Lookup_1--data.Q2 Lookup_3--data.Q3

but I get an error (...Opened Exclusively...) And this is with a dummy
database with just those tables (closed) and this query.

here is the SQL

SELECT lookup.value, lookup_1.value, lookup_2.value, Data.User
FROM lookup AS lookup_2 INNER JOIN (lookup INNER JOIN (lookup AS lookup_1
INNER JOIN Data ON lookup_1.id = Data.Q2) ON lookup.id = Data.Q1) ON
lookup_2.id = Data.Q3;

Any Ideas?

Thanks

 

[Guest]

Try this using a table named Cross_Ref. If there is no match then the
results is 'Unknown.'

Cross_Ref ---
Indicator X-Ref
1 Yes
2 No
how How now brown cow?
x ABC_XYZ
Unknown -- the indicator is null in this record.

SELECT Database.Agent, Database.Biller, Cross_Ref.[X-Ref]
FROM Cross_Ref, [Database]
WHERE (((Cross_Ref.Indicator)=[CMT] Or (Cross_Ref.Indicator) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have Changed the names, but that is not the root of the problem, this is a
much simplified version of my problem.

The actual table has several instances of codes (in some cases 20) and each
lookup table has 20 values in it... IE

ProblemCatagory, ProblemCatagory1,...,ProblemCatagory16
All share values in ProblemCatagoryLookup (which has 20 Values in it).
This is repeated several times in the data, so you can see why I don't want
to have ifs, or make more than one lookup table.

The data is imported from a fixed column text file provided from an outside
source.

Also fyi the table being locked is the lookup table.

I have read that the multiple instances of the table should work, think it
may be a setting in record locking options?

Thanks.

Both 'value' and 'user' are reserved words. That can lead to errors. You
might try putting square brackets [ ] around the field names or changing the
field names to something not reserved.

Check out the following for reserved words and some of the problems they can
cause:
http://support.microsoft.com/kb/286335/

I'd be tempted to normalize the data using Union queries then running a
crosstab on it. In the long run, this would be best.

You could also just forget about the Lookup table and use IIf statements IF
1 and 2 were the only values.

Q1s: IIf([Q1] = 1, "Yes","No")
Q2s: IIf([Q2] = 1, "Yes","No")
Q3s: IIf([Q3] = 1, "Yes","No")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

This is an example of what I need to do. (The data is from an outside source
so I am stuck with format)

lookup
id | value
-------------
1 | Yes
2 | No

Data
User | Q1| Q2 | Q3|
U1 | 2 | 1 | 2 |
U2 | 1 | 1 | 1 |

I need it to display

U1 No | Yes | No
U2 Yes| Yes | Yes

I have tried using multiple instances of lookup

Lookup--data.Q1, Lookup_1--data.Q2 Lookup_3--data.Q3

but I get an error (...Opened Exclusively...) And this is with a dummy
database with just those tables (closed) and this query.

here is the SQL

SELECT lookup.value, lookup_1.value, lookup_2.value, Data.User
FROM lookup AS lookup_2 INNER JOIN (lookup INNER JOIN (lookup AS lookup_1
INNER JOIN Data ON lookup_1.id = Data.Q2) ON lookup.id = Data.Q1) ON
lookup_2.id = Data.Q3;

Any Ideas?

Thanks

 

[Guest]

Earlier post will not work correct.
--
KARL DEWEY
Build a little - Test a little

Try this using a table named Cross_Ref. If there is no match then the
results is 'Unknown.'

Cross_Ref ---
Indicator X-Ref
1 Yes
2 No
how How now brown cow?
x ABC_XYZ
Unknown -- the indicator is null in this record.

SELECT Database.Agent, Database.Biller, Cross_Ref.[X-Ref]
FROM Cross_Ref, [Database]
WHERE (((Cross_Ref.Indicator)=[CMT] Or (Cross_Ref.Indicator) Is Null));

--
KARL DEWEY
Build a little - Test a little

I have Changed the names, but that is not the root of the problem, this is a
much simplified version of my problem.

The actual table has several instances of codes (in some cases 20) and each
lookup table has 20 values in it... IE

ProblemCatagory, ProblemCatagory1,...,ProblemCatagory16
All share values in ProblemCatagoryLookup (which has 20 Values in it).
This is repeated several times in the data, so you can see why I don't want
to have ifs, or make more than one lookup table.

The data is imported from a fixed column text file provided from an outside
source.

Also fyi the table being locked is the lookup table.

I have read that the multiple instances of the table should work, think it
may be a setting in record locking options?

Thanks.

Both 'value' and 'user' are reserved words. That can lead to errors. You
might try putting square brackets [ ] around the field names or changing the
field names to something not reserved.

Check out the following for reserved words and some of the problems they can
cause:
http://support.microsoft.com/kb/286335/

I'd be tempted to normalize the data using Union queries then running a
crosstab on it. In the long run, this would be best.

You could also just forget about the Lookup table and use IIf statements IF
1 and 2 were the only values.

Q1s: IIf([Q1] = 1, "Yes","No")
Q2s: IIf([Q2] = 1, "Yes","No")
Q3s: IIf([Q3] = 1, "Yes","No")
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

This is an example of what I need to do. (The data is from an outside source
so I am stuck with format)

lookup
id | value
-------------
1 | Yes
2 | No

Data
User | Q1| Q2 | Q3|
U1 | 2 | 1 | 2 |
U2 | 1 | 1 | 1 |

I need it to display

U1 No | Yes | No
U2 Yes| Yes | Yes

I have tried using multiple instances of lookup

Lookup--data.Q1, Lookup_1--data.Q2 Lookup_3--data.Q3

but I get an error (...Opened Exclusively...) And this is with a dummy
database with just those tables (closed) and this query.

here is the SQL

SELECT lookup.value, lookup_1.value, lookup_2.value, Data.User
FROM lookup AS lookup_2 INNER JOIN (lookup INNER JOIN (lookup AS lookup_1
INNER JOIN Data ON lookup_1.id = Data.Q2) ON lookup.id = Data.Q1) ON
lookup_2.id = Data.Q3;

Any Ideas?

Thanks