Query disappears after adding table to design view

[Darrin Ezell]

Hi all, first post here on the board. I've searched but cannot find a related
inquiry about this.

I've got a query that I'm working on (Access 2003) and when I add a table in
design view showing relationships, when I go back to view the data it's gone.
Needless to say, when I write my expression, there is no data there either.
I've isolated the problem to first occur after adding the table in the
relationship view in design mode. I'm "wheels off" trying figure this one
out and it is probably a simple solution however I can't seem to get a clue.
Any help would be appreciated.

 

[Guest]

Are you saying that after adding a table in the query 'design view showing
relationships' and save it then re-open in design view the table is not shown?

Or you saying that when you run the query the data is not displayed?

 

[Darrin Ezell]

Karl,

Thank you for your reply.

Are you saying that after adding a table in the query 'design view showing
relationships' and save it then re-open in design view the table is not shown?

No, it will show the table I've added into the view however just simply
adding the table deletes any data that was there before. Running the query
again makes no difference. Even after adding in my expression and running the
query, no data.

Or you saying that when you run the query the data is not displayed?

When I take the table away and run it, the data comes back. If there is a
table in design view of the query regarding relationships that has nothing to
do with the query, does that make any difference?

I've tried isolating the problem but can't seem to. Everything in the
expression looks to be right although looks are usually deceiving.

 

[Guest]

adding the table deletes any data that was there before.
Are you saying it deletes the records from the table? Or are you saying
that the records that were previously pulled no long come up?

If the later is the case then your query has changed is all.

Post the query SQL before you add the table and then after you add the table
in the query. To do this open the query in design view, click on menu VIEW -
SQL View. Highlight, copy, and paste in a post.

 

[Darrin Ezell via AccessMonster.com]

Somehow I solved it. Now I'm stuck running through an overflow error. I've
been working on converting a database for a company as an extension of my
temp employment and it's gone from easy to problem filled.

I need to know how to write a IIF([blah], 0, Null, [blah]/[blah]) AS King
Blah statement. Here's the SQL:

Quantity and Application can be zero in reality but the return is an overflow
error.

SELECT Paint_Usage.Painting_INVID, Paint_Usage.BoothID, Paint_Usage.
Paint_MaterialID, Paint_Usage.Date, Paint_Usage.Quantity, Paint_Usage.UnitID,
Paint_Usage.Application, Sum([Quantity]*[ConvertGallon]) AS Usage_Gallons,
Sum((([Quantity]*[ConvertGallon])*[VOC])/[Application]) AS VOCHrs

The question is how do those statements work or where can I find explanations
on the net? I'm new at the SQL and pretty decent at the Access novice stuff.

Thanks for trying earlier.

Are you saying it deletes the records from the table? Or are you saying
that the records that were previously pulled no long come up?

If the later is the case then your query has changed is all.

Post the query SQL before you add the table and then after you add the table
in the query. To do this open the query in design view, click on menu VIEW -
SQL View. Highlight, copy, and paste in a post.

Karl,

[quoted text clipped - 16 lines]

I've tried isolating the problem but can't seem to. Everything in the
expression looks to be right although looks are usually deceiving.

--
Later,

Darrin

Message posted via AccessMonster.com

 

[Guest]

Somehow I solved it. Now I'm stuck running through an overflow error. I've
been working on converting a database for a company as an extension of my
temp employment and it's gone from easy to problem filled.

I need to know how to write a IIF([blah], 0, Null, [blah]/[blah]) AS King
Blah statement. Here's the SQL:

Quantity and Application can be zero in reality but the return is an overflow
error.

SELECT Paint_Usage.Painting_INVID, Paint_Usage.BoothID, Paint_Usage.
Paint_MaterialID, Paint_Usage.Date, Paint_Usage.Quantity, Paint_Usage.UnitID,
Paint_Usage.Application, Sum([Quantity]*[ConvertGallon]) AS Usage_Gallons,
Sum((([Quantity]*[ConvertGallon])*[VOC])/[Application]) AS VOCHrs

The question is how do those statements work or where can I find explanations
on the net? I'm new at the SQL and pretty decent at the Access novice stuff.

Thanks for trying earlier.

adding the table deletes any data that was there before.

Are you saying it deletes the records from the table? Or are you saying
that the records that were previously pulled no long come up?

If the later is the case then your query has changed is all.

Post the query SQL before you add the table and then after you add the table
in the query. To do this open the query in design view, click on menu VIEW -
SQL View. Highlight, copy, and paste in a post.

Karl,

[quoted text clipped - 16 lines]

I've tried isolating the problem but can't seem to. Everything in the
expression looks to be right although looks are usually deceiving.

--
Later,

Darrin

Message posted via AccessMonster.com

 

[Darrin Ezell via AccessMonster.com]

OK, Karl...I'm lost on your reply. I'm off of work from there until monday
but working with the Access stuff has just got my jizz going. I love working
on stuff that takes hours to realize my own knowledge faults. I pick most
stuff up quick however I'm primarily web based in design and not database
oriented. If anyone has a good center of reading or knowledge online that I
can go find the solutions rather than post or bother anyone here I'm all cool
with that. I'm a fan of RTFM but I don't have one. Currently, I'm PEBKAC.
hehehe.

Seriously, thank you for your time and I consider you a wonderful dude for
trying to help. I'm still not sure what I did differently other than go to a
mom and pop BBQ joint and come back to the keyboard.

Somehow I solved it. Now I'm stuck running through an overflow error. I've
been working on converting a database for a company as an extension of my

[quoted text clipped - 31 lines]

--
Later,

Darrin

Message posted via AccessMonster.com

 

[Guest]

My earlier post failed.
You will get an error if you divide by zero. I would think that there will
be an emission factor (application) and should be from 1 to 0.00000000001 for
complete evaporation to immeasurable.

Sum((([Quantity]*[ConvertGallon])*[VOC])/[Application]) AS VOCHrs

Use the following --
IIF([Quanity] = 0, 0,
Sum(([Quantity]*[ConvertGallon])*[VOC])/IIF([Application] Is Null Or
[Application] = 0, .00000000000001, [Application])) AS VOCHrs

Somehow I solved it. Now I'm stuck running through an overflow error. I've
been working on converting a database for a company as an extension of my
temp employment and it's gone from easy to problem filled.

I need to know how to write a IIF([blah], 0, Null, [blah]/[blah]) AS King
Blah statement. Here's the SQL:

Quantity and Application can be zero in reality but the return is an overflow
error.

SELECT Paint_Usage.Painting_INVID, Paint_Usage.BoothID, Paint_Usage.
Paint_MaterialID, Paint_Usage.Date, Paint_Usage.Quantity, Paint_Usage.UnitID,
Paint_Usage.Application, Sum([Quantity]*[ConvertGallon]) AS Usage_Gallons,
Sum((([Quantity]*[ConvertGallon])*[VOC])/[Application]) AS VOCHrs

The question is how do those statements work or where can I find explanations
on the net? I'm new at the SQL and pretty decent at the Access novice stuff.

Thanks for trying earlier.

adding the table deletes any data that was there before.

Are you saying it deletes the records from the table? Or are you saying
that the records that were previously pulled no long come up?

If the later is the case then your query has changed is all.

Post the query SQL before you add the table and then after you add the table
in the query. To do this open the query in design view, click on menu VIEW -
SQL View. Highlight, copy, and paste in a post.

Karl,

[quoted text clipped - 16 lines]

I've tried isolating the problem but can't seem to. Everything in the
expression looks to be right although looks are usually deceiving.

--
Later,

Darrin

Message posted via AccessMonster.com

 

[virtualkeeper via AccessMonster.com]

Karl,

If there is no quantity or application (application is the time in hours of
paint spray time), there shouldn't be any emission factor. Can't have
emissions for non-use basically. I'll throw that equation in there and see
what pops out anyway. Thanks for the help as it gives me a starting point to
get going again this week.

My earlier post failed.
You will get an error if you divide by zero. I would think that there will
be an emission factor (application) and should be from 1 to 0.00000000001 for
complete evaporation to immeasurable.

Sum((([Quantity]*[ConvertGallon])*[VOC])/[Application]) AS VOCHrs

Use the following --
IIF([Quanity] = 0, 0,
Sum(([Quantity]*[ConvertGallon])*[VOC])/IIF([Application] Is Null Or
[Application] = 0, .00000000000001, [Application])) AS VOCHrs

Somehow I solved it. Now I'm stuck running through an overflow error. I've
been working on converting a database for a company as an extension of my

[quoted text clipped - 31 lines]

--
Later,

Darrin

Message posted via AccessMonster.com

 

[virtualkeeper via AccessMonster.com]

Karl,

You're a genius! It worked like a charm. I changed the .000...01 to a zero
and it displays the information exactly how I want it. Now looking at how you
wrote the equation here is what I figure the code is saying, correct me if
I'm wrong:

IIF [Quantity], 0, 0 means if the quantity is zero then the value displayed
is zero and then proceed to the equation of Sum((blah, blah) until it gets to
[Application],0,0 whereby if the application is 0 then display a zero as VOC
hrs. There should never be a zero in quantity and a greater than zero number
in application or vice versa.

I was wondering how to write that statement and I think I've got a good idea
now. Thanks man.

Karl,

If there is no quantity or application (application is the time in hours of
paint spray time), there shouldn't be any emission factor. Can't have
emissions for non-use basically. I'll throw that equation in there and see
what pops out anyway. Thanks for the help as it gives me a starting point to
get going again this week.

My earlier post failed.
You will get an error if you divide by zero. I would think that there will

[quoted text clipped - 13 lines]

 

[Guest]

Not quite. IIF [Quantity], 0, 0 means if the quantity is zero then the value
displayed
is zero and then the second IIF is ignored - not processed.
If the quanity is not zero then evaluate the emission. The second part
evaluates the emission factor used and if there is none or zero (you can not
divide by zero) it substitues .00000001 for the zero.

Karl,

You're a genius! It worked like a charm. I changed the .000...01 to a zero
and it displays the information exactly how I want it. Now looking at how you
wrote the equation here is what I figure the code is saying, correct me if
I'm wrong:

IIF [Quantity], 0, 0 means if the quantity is zero then the value displayed
is zero and then proceed to the equation of Sum((blah, blah) until it gets to
[Application],0,0 whereby if the application is 0 then display a zero as VOC
hrs. There should never be a zero in quantity and a greater than zero number
in application or vice versa.

I was wondering how to write that statement and I think I've got a good idea
now. Thanks man.

Karl,

If there is no quantity or application (application is the time in hours of
paint spray time), there shouldn't be any emission factor. Can't have
emissions for non-use basically. I'll throw that equation in there and see
what pops out anyway. Thanks for the help as it gives me a starting point to
get going again this week.

My earlier post failed.
You will get an error if you divide by zero. I would think that there will

[quoted text clipped - 13 lines]

I've tried isolating the problem but can't seem to. Everything in the
expression looks to be right although looks are usually deceiving.