Two Tables, 1 Report

[athyeh]

Hello All,

I have two tables that report errors:

• TblOne: Inputted through a form
• TblTwo: Imported form a separate excel sheet (emailed every day)

The tables report the same TYPE of information: They are reporting errors that operators make.

However: they are dissimilar in structure, field headings, the way data is labelled and identified, etc.




Current:
I am using a query linked to a form to generate my report:

• The form allows the user to select Operator and Date (from TblOne)
• The "Submit" button on the form activates the query, whose criteria is the boxes on the form.
• Query pulls results from TblOne as desired.

Need:

• The form allows the user to select Operator and Date
• The "Submit" button on the form activates the query, whose criteria is the boxes on the form.
• Query pulls results from TblOne and TblTwo as desired.

Example:

TblOne Record Example

Field: [Operator] [Main Issue] [Location Found] [Date]
Barney Lost Wallet Drive-In 01/2/2013

TblTwo Record Example

Fields: [STA] [Error] [Amount] [State] [ID] [Date]
Barney Bank Robbery $100,000 NY 100034002 01/05/2012



Form:

Name (Combo Box): Barney
Date From: 01/01/2012
Date To: 02/01/2013

*Click* Submit

Query Returns:


[STA] [Error] [Amount] [State] [ID] [Date] [Main Issue] [Location Found]
Record 1: Barney Bank Robbery $100,000 NY 100034002 01/5/2012
Record 2: Barney 01/2/2013 Lost Wallet Drive-In

(Pre-Submission I had the fields all spread out with their respective information under each heading, submission has scrunched everything together)

Is this possible? Is there a better way to do this?

Your replies are appreciated.

[June7]

Are you saying you have a query that returns those records or you want to know how to do that?

If the latter, options:

1. import the worksheet data into table one

2. UNION query that unites the two tables into a single dataset that can be the RecordSource for report. There is no wizard/designer for UNION, must type into SQL View of query builder.

SELECT [Operator], [Main Issue], 0 AS Amount, [Location Found], "none" AS ID, [Date] FROM TblOne
UNION [STA], [Error], [Amount], [State], [ID], [Date] FROM TblTwo;

[athyeh]

Is there anyway to create the query so that STA and Operator combine into one field?

[athyeh]

Also want to clarify:

Table 1 Fields:
Operator, Main Issue, Location Found, Date

Table 2 Fields:
STA, Error, Amount, State, ID, DATE

In the SQL code would it be this:

SELECT [Operator], [Main Issue], [Location Found], [Date], "none" as STA, "none" as Error, 0 as Amount, "none" as State, "none" as [ID] FROM TblOne
UNION [STA], [Error], [Amount], [State], [ID], [Date], "none" as Operator, "none" as Main issue, "none" as Location Found FROM TblTwo;


?


Thank you for your reply!

[athyeh]

Also, how do you point the SQL statement to the Form for its criteria?

[June7]

The first line of the UNION sets the field names. Data must be in same order on each line. My suggested query will have the STA and Operator data in one field called Operator. Also, Main Issue and Error data will be in same column called [Main Issue]. You could use an alias for these two if you want.

SELECT [Operator], [Main Issue], 0 AS Amount, [Location Found], "none" AS State, "none" AS ID, [Date], "Table1" AS Source FROM TblOne
UNION [STA], [Error], [Amount], "none", [State], [ID], [Date], "Table2" FROM TblTwo;

If you use spaces in names, must enclose in []. Recommend avoid spaces and special characters (underscore is exception). Also avoid reserved words as names (Date is one).

[athyeh]

Absolutely beautiful.

You are legendary...

Two questions now:

How do we point to criteria created by the form?

Also re: criteria, how do we tell the query that Barney and 1133555 are synonymous and to pull both of these for "Barney" typed into text box on form?

[June7]

Can build another query that uses the UNION as data source.

Criteria parameter in query that references control on form, under pertinent fields:

LIKE Forms!formname!controlname & "*"

If you type "Barney", all records with "Barney" will pull, if you type "1133555", all records with "1133555" will pull. If you want to translate "Barney" as "Barney or 1133555" and vice versa, that is another issue and not so easy. What field holds 1133555?

[athyeh]

STA would hold 1133555

Person is identified by Name (Operator) or Number (STA)

[athyeh]

Right now this is what I have but it is not working at all. If I take out: FROM UNION: it retrieves all records from both tables.

WHERE [Operator]=[Forms]![frmOpReports]![Combo38], FROM UNION;

[June7]

Remove that comma. Use wildcard.

Suggest you give the UNION query a more descriptive name than UNION.

Your early example showed a name in the [STA] field.

Do you have a table associating "Barney" with "1133555"

[athyeh]

WHERE [Operator]=[Forms]![frmOpReports]![Combo38]*FROM UNION;

Returns "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

I just realized that and I apologize. I do have a table entitled, "tblNames"

[June7]

Type criteria exactly as shown with LIKE operator, ampersand, and quote marks on the Criteria row of query designer. Using LIKE and wildcard will return all records if combobox is blank except for records where field is null.

LIKE [Forms]![frmOpReports]![Combo38] & "*"

Include tblNames (assume has fields for name and numberID) in the UNION by join to each table. Also assume every name and numberID in the two tables found in tblNames:

SELECT tblNames.[Operator], tblNames.[numberID field] AS AcctID, [Main Issue], 0 AS Amount, [Location Found], "none" AS State, "none" AS ID, [Date], "Table1" AS Source FROM TblOne INNER JOIN tblNames ON tblOne.Operator = tblNames.Operator
UNION tblNames.[Operator], tblNames.[numberID field], [Error], [Amount], "none", [State], [ID], [Date], "Table2" FROM TblTwo INNER JOIN tblNames ON tblTwo.STA = tblNames.[numberID field];

Adjust the above as needed for correct field names. Can use the query designer to help build the individual queries then copy/paste the sql statements into SQL View for the UNION statement.

[athyeh]

Good morning,

Been trying to figure this out for the last day or so but no dice....

SELECT [Auditor], [Audit Date], tblNames.[Operator], tblNames.[Lan ID], [Location Found], [Loan Identifier], [Main Issue], [Issue 2], [Issue 3], "none" as [ST], "none" as [Loan Amount], "none" as [Org ID], "none" as [Last Name], [Auditor Notes], "Table1" AS Source FROM tblMainDB INNER JOIN tblNames ON tblMainDB.Operator = tblNames.Operator
UNION SELECT "Exceptions" as [Auditor], [Audit Date], tblNames.[Operator], tblNames.[Lan ID], "Exceptions" as [Location Found], [Loan], [Error Found], "none" as [Issue 2], "none" as [Issue 3], [ST], [Loan Amount], [Org ID], [Last Name], "none" as [Auditor Notes], "Table 2" FROM tblMainDB INNER JOIN tblNames ON tblExceptions.[Loan sta] = tblNames.[Lan ID];

Receives: "Syntax Error in JOIN Operation"

tblMainDB = "TblOne"
tblExceptions = "TblTwo"

[June7]

Use the query designer to get individual SELECT sql correct then copy/paste it into the UNION.

You should use 0 for Loan Amount. "None" forces the field to be treated as text type.