Looking for your expert help again please

  • Thread starter Thread starter WorkRelated
  • Start date Start date
W

WorkRelated

Me again :o)

I have linked an excel spreadsheet to my database as below
JobRef
Expr1
This now appears as a table.

I have a form in my database
JobRef
PurchaseCost

I would like to display the total cost of purchases in the PurchaseCost
field for each JobRef

So if the JobRef in the table matches the JobRef in the form add together
Expr1 from the form and display the total.

I know what I want it to do but I can't get it to work. Please help
 
Remember, a form only displays data from a table. Unless it is unbound, it
doesn't have any data of it's own. Unbound forms or controls are not the
default.

If you write a query, that uses the aggregate functions (the Sigma sign in
the query design view) you would Group By JobRef and Sum the PurchaseCost
field. You can then use a form, or report, to display the results of the
query.
 
WorkRelated said:
Me again :o)

I have linked an excel spreadsheet to my database as below
JobRef
Expr1
This now appears as a table.

I have a form in my database
JobRef
PurchaseCost

I would like to display the total cost of purchases in the PurchaseCost
field for each JobRef

So if the JobRef in the table matches the JobRef in the form add together
Expr1 from the form and display the total.

I know what I want it to do but I can't get it to work. Please help
Click to expand...
 
Thanks for your reply. I thought that I could use the iif sum function and
refer back to table but i take it this is not possible.

This is a database that i have been using for a few months that i would like
to add this too.
 
I shall try and explain what I am trying to achieve

I have 2 Databases one is a Purchase Order Database which is has an order
table (bound to form) and an order detail table (bound to subform). The Job
Ref field is in the main form and the subform contains the price of each item
purchased.

The other database is a Job Costing Database this has a main table (bound to
form) and product detail table (bound to subform). The main form has a field
for Job Ref to be entered and a field for the total purchases for the job.

At present we have to run a report every month in the Purchase Order
database that calculates the total purchases per job number, then enter the
figure in the job costing database. (As our jobs can run over a length of
time the figures do change month to month) This report is now getting quite
large and the process time consuming so i was looking for a way for the Job
Costing database to update automatically from the Purchase Order database.

So far I have run the report in Purchase Orders and have exported it to
Excel I have then linked the Excel spreadsheet to the Job Costings as a
table. My plan was to run the report in Purchase Orders and each time I
export it to excel replace the existing spreadsheet so that the linked table
in the Job Costings is also updated.

I am struggling with how to get the total figure displayed in the Job
Costing Form. I had already raised a query which totals each job in the Job
Costing from the linked Excel table but I am not sure how to take these
totals and display them in the Job Costings main form

I hope this makes sense. I am self taught on Access using forums like this
to help me so I have no doubt done this the long way round but it is not
until i have been using a database for x amount of time that i think oh it
would be nice if it could do this.
 
WorkRelated said:
Me again :o)

I have linked an excel spreadsheet to my database as below
JobRef
Expr1
This now appears as a table.

I have a form in my database
JobRef
PurchaseCost

I would like to display the total cost of purchases in the PurchaseCost
field for each JobRef

So if the JobRef in the table matches the JobRef in the form add together
Expr1 from the form and display the total.

I know what I want it to do but I can't get it to work. Please help
Click to expand...
 
Hi
Thank you for your reply. I have tryed to use the DLookup function in the
control source of the form but I can not get it to work can you please help.

the query is called PurchaseQuery with [JobRef] & [SumOfExpr1]
the form is called Main with [JobRef] and [PurchaseCost]

I want [SumOfExpr1] to show in [PurchaseCost] if the [JobRef] from Main
matches the [JobRef] in PurchaseQuery

KenSheridan via AccessMonster.com said:
Using your existing method, as you already have the total per job computed in
a query you can just look it up for the cuurent job in an unbound text box on
the form with an expression such as this as its ControlSource property:

=DLookup("[TotalCost]", "[YourQuery]", "[Job Ref] = " & [Job Ref])

or if Job Ref is a text data type rather than a number data type:

=DLookup("[TotalCost]", "[YourQuery]", "[Job Ref] = """ & [Job Ref] & """")

You could probably do it directly from the data in the Job Costing Database
by linking to its tables in the Purchase Order Database and creating the
necessary query in the ob Costing Database.

But the best solution would be to incorporate all the tables from both
databases into a single new 'back end' file. You can then replace the real
tables in the two existing databases with links to the tables in the back end
file. This way you can also create enforced relationships between the tables
as appropriate in the back end so as to protect the integrity of the data.
It would also mean that, if the back end file is placed in a shared location
on the system, it can be shared by multiple users, each using their own copy
of one and/or the other front ends.

Ken Sheridan
Stafford, England
I shall try and explain what I am trying to achieve

I have 2 Databases one is a Purchase Order Database which is has an order
table (bound to form) and an order detail table (bound to subform). The Job
Ref field is in the main form and the subform contains the price of each item
purchased.

The other database is a Job Costing Database this has a main table (bound to
form) and product detail table (bound to subform). The main form has a field
for Job Ref to be entered and a field for the total purchases for the job.

At present we have to run a report every month in the Purchase Order
database that calculates the total purchases per job number, then enter the
figure in the job costing database. (As our jobs can run over a length of
time the figures do change month to month) This report is now getting quite
large and the process time consuming so i was looking for a way for the Job
Costing database to update automatically from the Purchase Order database.

So far I have run the report in Purchase Orders and have exported it to
Excel I have then linked the Excel spreadsheet to the Job Costings as a
table. My plan was to run the report in Purchase Orders and each time I
export it to excel replace the existing spreadsheet so that the linked table
in the Job Costings is also updated.

I am struggling with how to get the total figure displayed in the Job
Costing Form. I had already raised a query which totals each job in the Job
Costing from the linked Excel table but I am not sure how to take these
totals and display them in the Job Costings main form

I hope this makes sense. I am self taught on Access using forums like this
to help me so I have no doubt done this the long way round but it is not
until i have been using a database for x amount of time that i think oh it
would be nice if it could do this.
In a general sort of way you could create a totals query grouped on JobRef,
with a Sum for Expr1. You could also use a form/subform, or show the total
Click to expand...
[quoted text clipped - 20 lines]
I know what I want it to do but I can't get it to work. Please help
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much, I got it working, I just relised that one is called
JobNumber rather than Job Ref that is why I could not get it too work. Thanks
again

KenSheridan via AccessMonster.com said:
It depends on whether JobRef is a number or text data type. If it’s a number
the ControlSource for the PurchaseCost control on the Main form would be:

=DLookup("[SumOfExpr1]", "[PurchaseQuery]", "[JobRef] = " & [JobRef])

If its a text data type the value needs to wrapped in quotes characters, so
it would be:

=DLookup("[SumOfExpr1]", "[PurchaseQuery]", "[JobRef] = """ & [JobRef] & """")


What this is doing is concatenating the current value of the JobRef control
in the form into an expression as the criterion for looking up the value of
the SumOfExpr1 column in the query, so if the current JobRef is 123 as a
number data type the criterion would evaluate to JobRef = 123; if it’s a text
data type, to JobRef = "123". The literal quotes characters are represented
in the expression "[JobRef] = """ & [JobRef] & """" by the two pairs of
contiguous quotes characters within the quotes characters which delimit the
strings. This is how you include a quotes characters in a string which is
itself already delimited by quotes characters.

Ken Sheridan
Stafford, England
Hi
Thank you for your reply. I have tryed to use the DLookup function in the
control source of the form but I can not get it to work can you please help.

the query is called PurchaseQuery with [JobRef] & [SumOfExpr1]
the form is called Main with [JobRef] and [PurchaseCost]

I want [SumOfExpr1] to show in [PurchaseCost] if the [JobRef] from Main
matches the [JobRef] in PurchaseQuery
Using your existing method, as you already have the total per job computed in
a query you can just look it up for the cuurent job in an unbound text box on
Click to expand...
[quoted text clipped - 61 lines]
I know what I want it to do but I can't get it to work. Please help
Click to expand...
Click to expand...
Click to expand...
 
Hi again, thank you so much for your help so far but I am going to ask for
more. I am using

=DLookup("[SumOfExpr1]", "[PurchaseQuery]", "[JobRef] = """ & [JobNumber] &
"""")

and it is working fine untill the job number does not exist in the table
then i am getting a blank field what i want is a 0 to be displayed if there
is no match. I have been reading about it and it looks like you can combine
an If and a DLookup but not sure how to word it.

Vicki

KenSheridan via AccessMonster.com said:
It depends on whether JobRef is a number or text data type. If it’s a number
the ControlSource for the PurchaseCost control on the Main form would be:

=DLookup("[SumOfExpr1]", "[PurchaseQuery]", "[JobRef] = " & [JobRef])

If its a text data type the value needs to wrapped in quotes characters, so
it would be:

=DLookup("[SumOfExpr1]", "[PurchaseQuery]", "[JobRef] = """ & [JobRef] & """")


What this is doing is concatenating the current value of the JobRef control
in the form into an expression as the criterion for looking up the value of
the SumOfExpr1 column in the query, so if the current JobRef is 123 as a
number data type the criterion would evaluate to JobRef = 123; if it’s a text
data type, to JobRef = "123". The literal quotes characters are represented
in the expression "[JobRef] = """ & [JobRef] & """" by the two pairs of
contiguous quotes characters within the quotes characters which delimit the
strings. This is how you include a quotes characters in a string which is
itself already delimited by quotes characters.

Ken Sheridan
Stafford, England
Hi
Thank you for your reply. I have tryed to use the DLookup function in the
control source of the form but I can not get it to work can you please help.

the query is called PurchaseQuery with [JobRef] & [SumOfExpr1]
the form is called Main with [JobRef] and [PurchaseCost]

I want [SumOfExpr1] to show in [PurchaseCost] if the [JobRef] from Main
matches the [JobRef] in PurchaseQuery
Using your existing method, as you already have the total per job computed in
a query you can just look it up for the cuurent job in an unbound text box on
Click to expand...
[quoted text clipped - 61 lines]
I know what I want it to do but I can't get it to work. Please help
Click to expand...
Click to expand...
Click to expand...
 
and it is working fine untill the job number does not exist in the table
then i am getting a blank field what i want is a 0 to be displayed if there
is no match. I have been reading about it and it looks like you can combine
an If and a DLookup but not sure how to word it.
Click to expand...

No If is needed - use the NZ() function instead, it converts Null To Zero:

=NZ(DLookup("[SumOfExpr1]", "[PurchaseQuery]", "[JobRef] = """ & [JobNumber] &
""""))
 
Thank you for that.

John W. Vinson said:
and it is working fine untill the job number does not exist in the table
then i am getting a blank field what i want is a 0 to be displayed if there
is no match. I have been reading about it and it looks like you can combine
an If and a DLookup but not sure how to word it.
Click to expand...

No If is needed - use the NZ() function instead, it converts Null To Zero:

=NZ(DLookup("[SumOfExpr1]", "[PurchaseQuery]", "[JobRef] = """ & [JobNumber] &
""""))
Click to expand...
 
Back
Top