Selecting different tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am trying to combine an Access program that we have at different sites
into a single one. I have been able to accomplish almost all of it but have
hit a snag when when running the history look up form. I have tables labeld
"History_Table_Birdsall" or "HIstory_Table_Cascade" etc., and a query that
returns the date, number, person and reason in a combo box that when choosen
fills in the rest of the original form. I have been trying to get the form
caption (an option group on the side allows for changes) to pick the table to
use. Is there any way of putting an expression in the query to accomplish
this?
 
Jim

Are you saying that you have "equivalent" tables (same structure) with
different names ("...Birdsall", "...Cascade", ...)?

If so, consider doing a bit more normalizing on your data before trying to
query it from your form.

A single table with the same/equivalent structure, PLUS a field designating
the "source" (or whatever the Birdsall, Cascade, ... represents) would let
you put all your records in one table. Querying this is MUCH easier than
trying to find the right table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
I think you mean that the tablename for the query is selectable via the
option group. Because Option groups are number based, you would need to do
an IF...Then or Select Case to discern.

dim strTable as string

Select Case ogTable
case 1 : strTable = "History_Table_Birdsall"
case 2: strTable = "HIstory_Table_Cascade"
End Select

strSql = "Select * from " & strTable & " WHERE ..."

Use the strSql value to populate a form, subform, or report.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
 
Thanks, I have gotten that far but having the form and combo box record (and
row) source change as different tables are identified. I have been trying to
have the queries choose a table in a IF-Then stament:

SELECT DISTINCTROW First(History_Table_Birdsall.date) AS [date Field],
First(History_Table_Birdsall.number) AS [number Field],
First(History_Table_Birdsall.reason) AS [reason Field],
First(History_Table_Birdsall.[issued to]) AS [issued to Field],
Count(History_Table_Birdsall.date) AS NumberOfDups
FROM History_Table_Birdsall
GROUP BY History_Table_Birdsall.date, History_Table_Birdsall.number,
History_Table_Birdsall.reason, History_Table_Birdsall.[issued to]
HAVING (((Count(History_Table_Birdsall.date))>1) AND
((Count(History_Table_Birdsall.[issued to]))>=0));
 
One thing you can do to make life simpler. Use an alias for the table name.

IT doesn't make a lot of difference in a query with only one since you can refer
to fields without the table name prefixed.

SELECT DISTINCTROW First(A.date) AS [date Field],
First(A.number) AS [number Field],
First(A.reason) AS [reason Field],
First(A.[issued to]) AS [issued to Field],
Count(A.date) AS NumberOfDups
FROM History_Table_Birdsall AS A
GROUP BY A.date, A.number,
A.reason, A.[issued to]
HAVING (((Count(A.date))>1) AND
((A.[issued to]))>=0));

Then all you have to replace in the string is the one instance of a table name.
Dim strTableName as string
Dim strSQL as string

strTableName = "History_Table_BirdsAll"

StrSQL = "SELECT DISTINCTROW First(A.date) AS [date Field],
First(A.number) AS [number Field],
First(A.reason) AS [reason Field],
First(A.[issued to]) AS [issued to Field],
Count(A.date) AS NumberOfDups
FROM [" & strTableName "] & AS A
GROUP BY A.date, A.number,
A.reason, A.[issued to]
HAVING (((Count(A.date))>1) AND
((A.[issued to]))>=0))"


I would observe that I think your query is quite strange. I would write it as

SELECT A.date AS [date Field],
A.number AS [number Field],
A.reason AS [reason Field],
A.[issued to] AS [issued to Field],
Count(A.date) AS NumberOfDups
FROM History_Table_Birdsall AS A
WHERE A.[Issued To] >= 0
GROUP BY A.date, A.number,
A.reason, A.[issued to]
HAVING Count(A.date)>1

Thanks, I have gotten that far but having the form and combo box record (and
row) source change as different tables are identified. I have been trying to
have the queries choose a table in a IF-Then stament:

SELECT DISTINCTROW First(History_Table_Birdsall.date) AS [date Field],
First(History_Table_Birdsall.number) AS [number Field],
First(History_Table_Birdsall.reason) AS [reason Field],
First(History_Table_Birdsall.[issued to]) AS [issued to Field],
Count(History_Table_Birdsall.date) AS NumberOfDups
FROM History_Table_Birdsall
GROUP BY History_Table_Birdsall.date, History_Table_Birdsall.number,
History_Table_Birdsall.reason, History_Table_Birdsall.[issued to]
HAVING (((Count(History_Table_Birdsall.date))>1) AND
((Count(History_Table_Birdsall.[issued to]))>=0));

[MVP] S.Clark said:
I think you mean that the tablename for the query is selectable via the
option group. Because Option groups are number based, you would need to do
an IF...Then or Select Case to discern.

dim strTable as string

Select Case ogTable
case 1 : strTable = "History_Table_Birdsall"
case 2: strTable = "HIstory_Table_Cascade"
End Select

strSql = "Select * from " & strTable & " WHERE ..."

Use the strSql value to populate a form, subform, or report.


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html
Click to expand...
Click to expand...
 
Jim

For what it's worth, if your boss wants multiple/different tables, perhaps
your boss wants to build the application. Tables are not spreadsheets, and
I suspect your boss wants to be able to look directly at the tables.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Back
Top