join, update one side, not other?

R

r

Is it possible to have a query that joins two tables where table1 may not
have a matching record to table2, and in this event you could CREATE the
matching record in table1 by adding data to a field?

For example:

Table1 (one record per ID/CAT combo):
ID CAT Budget


Table2 (multiple records for ID/CAT combos):
ID CAT Checks


Display (grouped by ID/CAT):

ID CAT Budget SumOfChecks


All of Table2 data would display (all "SumOfChecks" along with ID and CAT),
and if there was no matching record in Table1, we could insert a value into
the Budget field and ADD a matching Table1 record, but not affect the Table2
data in any way.

??

Thanks for any help!
 
G

Guest

There "usually" is a budget before "checks" and therefore table1 would have a
record. In this case you need to run an unmatched query first and build
records for table1.

Then run a summation query.
 
G

Guest

SELECT Table1.ID, Table1.CAT, Table1.BUDGET, Sum(Table2.CHECKS) AS SumOfCHECKS
FROM Table1 LEFT JOIN Table2 ON (Table1.CAT = Table2.CAT) AND (Table1.ID =
Table2.ID)
GROUP BY Table1.ID, Table1.CAT, Table1.BUDGET;
 
R

r

Yes, usually the budget comes first. However, we want to catch any
categories that were paid against OUTSIDE of the set budget. Sometimes
invoices come in, and the staff will enter them and they don't have access
to the budget or even care about it. I'm trying to catch anything that
might sneak in.

I don't want to create a budget for EACH category off the bat because not
every job will use each category - would create wasted, unused records.

So, doable?
 
G

Guest

I think this will do what you want.

SELECT Table2.ID, Table2.CAT, Table1.BUDGET, Sum(Table2.CHECKS) AS SumOfCHECKS
FROM Table1 RIGHT JOIN Table2 ON (Table1.CAT = Table2.CAT) AND (Table1.ID =
Table2.ID)
GROUP BY Table2.ID, Table2.CAT, Table1.BUDGET;
 
R

r

Thanks, Karl. I tried it, but I still can't edit the budget field. Here's
my query:

SELECT [JOBHIST Temp].[hist_job_prefix] & [JOBHIST Temp].[hist_job_no] AS
jobnum, [JOBHIST Temp].HIST_JOB_SUFFIX, Bud_CatBudgets.CatBudget,
sum([JOBHIST Temp].HIST_DIST_AMOUNT) AS SumofChecks

FROM Bud_CatBudgets RIGHT JOIN [JOBHIST Temp] ON ( [JOBHIST
Temp].[hist_job_prefix] & [JOBHIST
Temp].[hist_job_no]=Bud_CatBudgets.CustomerID ) AND ( [JOBHIST
Temp].HIST_JOB_SUFFIX=Bud_CatBudgets.CategoryID )

GROUP BY[hist_job_prefix] & [hist_job_no], [JOBHIST
Temp].Hist_Job_suffix,Bud_CatBudgets.CatBudget;

Have I done something wrong, or is this just really not doable?

Thanks.
 
G

Guest

You can not edit it as it does not exist. There is no record.

Do you want it to automatically create a record if not exist?

r said:
Thanks, Karl. I tried it, but I still can't edit the budget field. Here's
my query:

SELECT [JOBHIST Temp].[hist_job_prefix] & [JOBHIST Temp].[hist_job_no] AS
jobnum, [JOBHIST Temp].HIST_JOB_SUFFIX, Bud_CatBudgets.CatBudget,
sum([JOBHIST Temp].HIST_DIST_AMOUNT) AS SumofChecks

FROM Bud_CatBudgets RIGHT JOIN [JOBHIST Temp] ON ( [JOBHIST
Temp].[hist_job_prefix] & [JOBHIST
Temp].[hist_job_no]=Bud_CatBudgets.CustomerID ) AND ( [JOBHIST
Temp].HIST_JOB_SUFFIX=Bud_CatBudgets.CategoryID )

GROUP BY[hist_job_prefix] & [hist_job_no], [JOBHIST
Temp].Hist_Job_suffix,Bud_CatBudgets.CatBudget;

Have I done something wrong, or is this just really not doable?

Thanks.





KARL DEWEY said:
I think this will do what you want.

SELECT Table2.ID, Table2.CAT, Table1.BUDGET, Sum(Table2.CHECKS) AS SumOfCHECKS
FROM Table1 RIGHT JOIN Table2 ON (Table1.CAT = Table2.CAT) AND (Table1.ID =
Table2.ID)
GROUP BY Table2.ID, Table2.CAT, Table1.BUDGET;
Click to expand...
Click to expand...
 
R

r

yes ... so will that require running the unmatched query first?

KARL DEWEY said:
You can not edit it as it does not exist. There is no record.

Do you want it to automatically create a record if not exist?

r said:
Thanks, Karl. I tried it, but I still can't edit the budget field. Here's
my query:

SELECT [JOBHIST Temp].[hist_job_prefix] & [JOBHIST Temp].[hist_job_no] AS
jobnum, [JOBHIST Temp].HIST_JOB_SUFFIX, Bud_CatBudgets.CatBudget,
sum([JOBHIST Temp].HIST_DIST_AMOUNT) AS SumofChecks

FROM Bud_CatBudgets RIGHT JOIN [JOBHIST Temp] ON ( [JOBHIST
Temp].[hist_job_prefix] & [JOBHIST
Temp].[hist_job_no]=Bud_CatBudgets.CustomerID ) AND ( [JOBHIST
Temp].HIST_JOB_SUFFIX=Bud_CatBudgets.CategoryID )

GROUP BY[hist_job_prefix] & [hist_job_no], [JOBHIST
Temp].Hist_Job_suffix,Bud_CatBudgets.CatBudget;

Have I done something wrong, or is this just really not doable?

Thanks.





KARL DEWEY said:
I think this will do what you want.

SELECT Table2.ID, Table2.CAT, Table1.BUDGET, Sum(Table2.CHECKS) AS SumOfCHECKS
FROM Table1 RIGHT JOIN Table2 ON (Table1.CAT = Table2.CAT) AND
Click to expand...
(Table1.ID
=
Table2.ID)
GROUP BY Table2.ID, Table2.CAT, Table1.BUDGET;

:

Yes, usually the budget comes first. However, we want to catch any
categories that were paid against OUTSIDE of the set budget. Sometimes
invoices come in, and the staff will enter them and they don't have access
to the budget or even care about it. I'm trying to catch anything that
might sneak in.

I don't want to create a budget for EACH category off the bat
Click to expand...
because
not
every job will use each category - would create wasted, unused records.

So, doable?

There "usually" is a budget before "checks" and therefore table1 would
have a
record. In this case you need to run an unmatched query first and build
records for table1.

Then run a summation query.

:

Is it possible to have a query that joins two tables where
Click to expand...
table1
may
not
have a matching record to table2, and in this event you could
Click to expand...
CREATE
the
matching record in table1 by adding data to a field?

For example:

Table1 (one record per ID/CAT combo):
ID CAT Budget


Table2 (multiple records for ID/CAT combos):
ID CAT Checks


Display (grouped by ID/CAT):

ID CAT Budget SumOfChecks


All of Table2 data would display (all "SumOfChecks" along with
Click to expand...
ID
and
CAT),
and if there was no matching record in Table1, we could insert a value
into
the Budget field and ADD a matching Table1 record, but not
Click to expand...
affect
the
Table2
data in any way.

??

Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
R

r

I got it.... did an unmatched and created any needed records. Thanks for
all the help...!


r said:
yes ... so will that require running the unmatched query first?

KARL DEWEY said:
You can not edit it as it does not exist. There is no record.

Do you want it to automatically create a record if not exist?

r said:
Thanks, Karl. I tried it, but I still can't edit the budget field. Here's
my query:

SELECT [JOBHIST Temp].[hist_job_prefix] & [JOBHIST Temp].[hist_job_no] AS
jobnum, [JOBHIST Temp].HIST_JOB_SUFFIX, Bud_CatBudgets.CatBudget,
sum([JOBHIST Temp].HIST_DIST_AMOUNT) AS SumofChecks

FROM Bud_CatBudgets RIGHT JOIN [JOBHIST Temp] ON ( [JOBHIST
Temp].[hist_job_prefix] & [JOBHIST
Temp].[hist_job_no]=Bud_CatBudgets.CustomerID ) AND ( [JOBHIST
Temp].HIST_JOB_SUFFIX=Bud_CatBudgets.CategoryID )

GROUP BY[hist_job_prefix] & [hist_job_no], [JOBHIST
Temp].Hist_Job_suffix,Bud_CatBudgets.CatBudget;

Have I done something wrong, or is this just really not doable?

Thanks.





I think this will do what you want.

SELECT Table2.ID, Table2.CAT, Table1.BUDGET, Sum(Table2.CHECKS) AS
SumOfCHECKS
FROM Table1 RIGHT JOIN Table2 ON (Table1.CAT = Table2.CAT) AND (Table1.ID
=
Table2.ID)
GROUP BY Table2.ID, Table2.CAT, Table1.BUDGET;

:

Yes, usually the budget comes first. However, we want to catch any
categories that were paid against OUTSIDE of the set budget. Sometimes
invoices come in, and the staff will enter them and they don't have
access
to the budget or even care about it. I'm trying to catch anything that
might sneak in.

I don't want to create a budget for EACH category off the bat because
not
every job will use each category - would create wasted, unused records.

So, doable?

There "usually" is a budget before "checks" and therefore table1 would
have a
record. In this case you need to run an unmatched query first and
build
records for table1.

Then run a summation query.

:

Is it possible to have a query that joins two tables where table1
may
not
have a matching record to table2, and in this event you could CREATE
the
matching record in table1 by adding data to a field?

For example:

Table1 (one record per ID/CAT combo):
ID CAT Budget


Table2 (multiple records for ID/CAT combos):
ID CAT Checks


Display (grouped by ID/CAT):

ID CAT Budget SumOfChecks


All of Table2 data would display (all "SumOfChecks" along with ID
and
CAT),
and if there was no matching record in Table1, we could insert a
value
into
the Budget field and ADD a matching Table1 record, but not affect
the
Table2
data in any way.

??

Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
R

r

Or not.... hm.

Here's what I've got to create records (was going to filter on as-needed
basis, but can't get even the whole thing to run):

UPDATE Bud_CatItemsGrouped LEFT JOIN Bud_CatBudgets ON
(Bud_CatItemsGrouped.HIST_JOB_SUFFIX = Bud_CatBudgets.CategoryID) AND
(Bud_CatItemsGrouped.jobnum = Bud_CatBudgets.CustomerID) SET
Bud_CatBudgets.CustomerID = [jobnum], Bud_CatBudgets.CategoryID =
[hist_job_suffix]

WHERE (((Bud_CatBudgets.CustomerID) Is Null) AND
((Bud_CatBudgets.CategoryID) Is Null));

I get a message that the operation must be an updatable query. ??



KARL DEWEY said:
You can not edit it as it does not exist. There is no record.

Do you want it to automatically create a record if not exist?

r said:
Thanks, Karl. I tried it, but I still can't edit the budget field. Here's
my query:

SELECT [JOBHIST Temp].[hist_job_prefix] & [JOBHIST Temp].[hist_job_no] AS
jobnum, [JOBHIST Temp].HIST_JOB_SUFFIX, Bud_CatBudgets.CatBudget,
sum([JOBHIST Temp].HIST_DIST_AMOUNT) AS SumofChecks

FROM Bud_CatBudgets RIGHT JOIN [JOBHIST Temp] ON ( [JOBHIST
Temp].[hist_job_prefix] & [JOBHIST
Temp].[hist_job_no]=Bud_CatBudgets.CustomerID ) AND ( [JOBHIST
Temp].HIST_JOB_SUFFIX=Bud_CatBudgets.CategoryID )

GROUP BY[hist_job_prefix] & [hist_job_no], [JOBHIST
Temp].Hist_Job_suffix,Bud_CatBudgets.CatBudget;

Have I done something wrong, or is this just really not doable?

Thanks.





KARL DEWEY said:
I think this will do what you want.

SELECT Table2.ID, Table2.CAT, Table1.BUDGET, Sum(Table2.CHECKS) AS SumOfCHECKS
FROM Table1 RIGHT JOIN Table2 ON (Table1.CAT = Table2.CAT) AND
Click to expand...
(Table1.ID
=
Table2.ID)
GROUP BY Table2.ID, Table2.CAT, Table1.BUDGET;

:

Yes, usually the budget comes first. However, we want to catch any
categories that were paid against OUTSIDE of the set budget. Sometimes
invoices come in, and the staff will enter them and they don't have access
to the budget or even care about it. I'm trying to catch anything that
might sneak in.

I don't want to create a budget for EACH category off the bat
Click to expand...
because
not
every job will use each category - would create wasted, unused records.

So, doable?

There "usually" is a budget before "checks" and therefore table1 would
have a
record. In this case you need to run an unmatched query first and build
records for table1.

Then run a summation query.

:

Is it possible to have a query that joins two tables where
Click to expand...
table1
may
not
have a matching record to table2, and in this event you could
Click to expand...
CREATE
the
matching record in table1 by adding data to a field?

For example:

Table1 (one record per ID/CAT combo):
ID CAT Budget


Table2 (multiple records for ID/CAT combos):
ID CAT Checks


Display (grouped by ID/CAT):

ID CAT Budget SumOfChecks


All of Table2 data would display (all "SumOfChecks" along with
Click to expand...
ID
and
CAT),
and if there was no matching record in Table1, we could insert a value
into
the Budget field and ADD a matching Table1 record, but not
Click to expand...
affect
the
Table2
data in any way.

??

Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
R

r

Nevermind - I was trying to do an UPDATE, not an append. It's working now.
Thanks again!

r said:
Or not.... hm.

Here's what I've got to create records (was going to filter on as-needed
basis, but can't get even the whole thing to run):

UPDATE Bud_CatItemsGrouped LEFT JOIN Bud_CatBudgets ON
(Bud_CatItemsGrouped.HIST_JOB_SUFFIX = Bud_CatBudgets.CategoryID) AND
(Bud_CatItemsGrouped.jobnum = Bud_CatBudgets.CustomerID) SET
Bud_CatBudgets.CustomerID = [jobnum], Bud_CatBudgets.CategoryID =
[hist_job_suffix]

WHERE (((Bud_CatBudgets.CustomerID) Is Null) AND
((Bud_CatBudgets.CategoryID) Is Null));

I get a message that the operation must be an updatable query. ??



KARL DEWEY said:
You can not edit it as it does not exist. There is no record.

Do you want it to automatically create a record if not exist?

r said:
Thanks, Karl. I tried it, but I still can't edit the budget field. Here's
my query:

SELECT [JOBHIST Temp].[hist_job_prefix] & [JOBHIST Temp].[hist_job_no] AS
jobnum, [JOBHIST Temp].HIST_JOB_SUFFIX, Bud_CatBudgets.CatBudget,
sum([JOBHIST Temp].HIST_DIST_AMOUNT) AS SumofChecks

FROM Bud_CatBudgets RIGHT JOIN [JOBHIST Temp] ON ( [JOBHIST
Temp].[hist_job_prefix] & [JOBHIST
Temp].[hist_job_no]=Bud_CatBudgets.CustomerID ) AND ( [JOBHIST
Temp].HIST_JOB_SUFFIX=Bud_CatBudgets.CategoryID )

GROUP BY[hist_job_prefix] & [hist_job_no], [JOBHIST
Temp].Hist_Job_suffix,Bud_CatBudgets.CatBudget;

Have I done something wrong, or is this just really not doable?

Thanks.





I think this will do what you want.

SELECT Table2.ID, Table2.CAT, Table1.BUDGET, Sum(Table2.CHECKS) AS
SumOfCHECKS
FROM Table1 RIGHT JOIN Table2 ON (Table1.CAT = Table2.CAT) AND (Table1.ID
=
Table2.ID)
GROUP BY Table2.ID, Table2.CAT, Table1.BUDGET;

:

Yes, usually the budget comes first. However, we want to catch any
categories that were paid against OUTSIDE of the set budget. Sometimes
invoices come in, and the staff will enter them and they don't have
access
to the budget or even care about it. I'm trying to catch anything that
might sneak in.

I don't want to create a budget for EACH category off the bat because
not
every job will use each category - would create wasted, unused records.

So, doable?

There "usually" is a budget before "checks" and therefore table1 would
have a
record. In this case you need to run an unmatched query first and
build
records for table1.

Then run a summation query.

:

Is it possible to have a query that joins two tables where table1
may
not
have a matching record to table2, and in this event you could CREATE
the
matching record in table1 by adding data to a field?

For example:

Table1 (one record per ID/CAT combo):
ID CAT Budget


Table2 (multiple records for ID/CAT combos):
ID CAT Checks


Display (grouped by ID/CAT):

ID CAT Budget SumOfChecks


All of Table2 data would display (all "SumOfChecks" along with ID
and
CAT),
and if there was no matching record in Table1, we could insert a
value
into
the Budget field and ADD a matching Table1 record, but not affect
the
Table2
data in any way.

??

Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query on two tables with matching null fields 4
Create a distinct Join 3
swapping data from one table to another 3
Merging data in a one-to-many relationship 5
Join based on part of a field 4
Conditional Query 1
Extract trades that do NOT match the other table 2
Join Problem 4

Top