join queries/tables

[kaosyeti]

hello. i have 2 queries, 1 that has daily data entered based on how many
customers a salesperson had that day and one that is monthly for how many
cars that person sold for a given month. i have a report that is based off
of the first query but now i want to add one field from the 2nd to be able to
calculate how many customers each salesperson saw vs. how many cars they sold
for a given time period (usually monthy). my problem is that the report
won't pull that field from the 2nd query (qrysoldunits). i've tried every
combination i can think of to join some of the fields in both the tables and
the queries but i think that i don't know enough about relationships to do it
right. for now, there's no defined relationships at all. there are also no
primary keys in any of my tables as none of the data is truly unique and may
be repeated in many records in each table.

i did try using dlookup as suggested by Marshall Barton but didn't get it to
work. i used =DLookUp([qrysoldunits]![units],[qrysoldunits]) as my
expression but no dice. the report is grouped by salesperson 1st and
department 2nd so i had this expression in a control in the department footer
section of the report -- that's why i didn't specifiy criteria. am i doing
it wrong?

any ideas? thanks.

 

[Michel Walsh]

Hi,

hello. i have 2 queries, 1 that has daily data entered based on how many
customers a salesperson had that day and one that is monthly for how many
cars that person sold for a given month.

Make a third query that would bring the two queries you have, and JOIN them
through the salesperson.


Base the report on that third query.


Hoping it may help,
Vanderghast, Access MVP

 

[kaosyeti]

i tried doing what you suggested and i got an error message that reads:

You have chosen fields from record sources which the wizard can't connect.
You may have chosen fields from a table and from a query based on that table.
If so, try choosing fields from only the table or only the query.

now i chose fields from two queries, so i don't know why i got this message.
the queries themselves do have fields from the same table (salesperson, for
example is a table that lists all the salespeople in a location, 5-10 usually,
and i did it that way so that it can be changed easily). any thoughts?

 

[Michel Walsh]

Hi,


You have that error message when you open the last query, in the query
designer, to see the data,
or
you have that error message when you build the report?


It may be a case of ambiguity (that would make viewing the data in the query
designer in error): the field you specify CAN come from multiple source. If
so, try to give the full name:

[tableName].[fieldName] or [queryName].[fieldName]

or, in the query, use alias to have a completely different column name.



Hoping it may help,
Vanderghast, Access MVP

 

[kaosyeti]

the error comes in when i'm building the report. i've been using the
expression builder and double-clicking on the field that i need and it fills
in something like [qrycontactdata]![Salesperson] for the salesperson field
on qrycontactdata. should i be manually typing qrycontactdata.salesperson
instead?

Greg

Hi,

You have that error message when you open the last query, in the query
designer, to see the data,
or
you have that error message when you build the report?

It may be a case of ambiguity (that would make viewing the data in the query
designer in error): the field you specify CAN come from multiple source. If
so, try to give the full name:

[tableName].[fieldName] or [queryName].[fieldName]

or, in the query, use alias to have a completely different column name.

Hoping it may help,
Vanderghast, Access MVP

i tried doing what you suggested and i got an error message that reads:

[quoted text clipped - 27 lines]

 

[Michel Walsh]

Hi,


*if* there is only one field salesperson in qrycontactdata, then it won't
hurt, indeed, to use

qrycontactdata.salesperson

if there is more than one field salesperson in qrycontactdata, it would be
preferable to use an alias, in qrycontactdata, to differentiate them among
themselves. To have an alias, use the keyword AS:

SELECT table1.salesPerson AS t1Person, table2.salesPerson AS
t2Person, ...

If this is impossible, use qrycontactdata.tablename.salesperson. That
syntax works only with JET (not with MS SQL Server). That is why using an
alias is preferred.


Hoping it may help,
Vanderghast, Access MVP

the error comes in when i'm building the report. i've been using the
expression builder and double-clicking on the field that i need and it
fills
in something like [qrycontactdata]![Salesperson] for the salesperson
field
on qrycontactdata. should i be manually typing qrycontactdata.salesperson
instead?

Greg

Hi,

You have that error message when you open the last query, in the query
designer, to see the data,
or
you have that error message when you build the report?

It may be a case of ambiguity (that would make viewing the data in the
query
designer in error): the field you specify CAN come from multiple source.
If
so, try to give the full name:

[tableName].[fieldName] or [queryName].[fieldName]

or, in the query, use alias to have a completely different column name.

Hoping it may help,
Vanderghast, Access MVP

i tried doing what you suggested and i got an error message that reads:

[quoted text clipped - 27 lines]

Hoping it may help,
Vanderghast, Access MVP

 

[kaosyeti]

actually, the problem was nowhere near the dlookup function. i had the vba
set up to close the form when the report opened. little did i know that the
report wouldn't pull the data from the form for anything past the first page
once the form is closed. it's hidden now and is no longer a problem. if
you're keen on knowing, the final expression looked like this:

=DLookUp("[units]","qrysoldunits","[salesperson]=""" & [Salesperson] & """
AND [Department]=""" & [Department] & """ AND ([monthandyear] Between #" &
Format([txtboxstartdate],"mm/dd/yyyy") & "# And #" & Format([txtboxenddate],
"mm/dd/yyyy") & "# )")

and it works just fine. i (obviously) didn't write this myself but it does
exactly what i wanted it to do. thanks again for you help.

Hi,

*if* there is only one field salesperson in qrycontactdata, then it won't
hurt, indeed, to use

qrycontactdata.salesperson

if there is more than one field salesperson in qrycontactdata, it would be
preferable to use an alias, in qrycontactdata, to differentiate them among
themselves. To have an alias, use the keyword AS:

SELECT table1.salesPerson AS t1Person, table2.salesPerson AS
t2Person, ...

If this is impossible, use qrycontactdata.tablename.salesperson. That
syntax works only with JET (not with MS SQL Server). That is why using an
alias is preferred.

Hoping it may help,
Vanderghast, Access MVP

the error comes in when i'm building the report. i've been using the
expression builder and double-clicking on the field that i need and it

[quoted text clipped - 31 lines]