Need help a query 1

[henniec]

Good day,

I have a query where I link two queries as a one to many. I have done similar queries in the past and every now and then I would find one where it would only give data from one table or query and not the other.

I have tried different scenarios and still the same thing happens. Started originally from two queries and link to what I need and run the query. Only data from the one query is displayed. Change the link the other way around same thing happens but now only data from the one that did not dispaly previous.

If I select the third option no data is displayed.

I made sure the dates and time are the same formatted in the same way, the result still come out the same. Changed the two queries to make table queries and used the resulting tables as my source I still can not get it work.

Why would this happen and am I missing something. I have tried this in different dabases using the same data with no difference.

Thanks.

Hennie

 

[randy700]

It would be much easier to help fix your code if we could SEE your code. Please post it.


Randy

 

[SkipVought]

henniec,

I notice that over the past 6 1/2 years, you have posted many threads and have received many good tips related to your stated needs. And you even have received [purple]five little purple stars.[/purple] Yet, you have responded NOT ONCE, to
[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The [purple]little purple Stars[/purple] accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

So please consider being part of the team.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Why did you mention

I made sure the dates and time are the same formatted in the same way

without telling us anything about your tables or fields.

Formatting is just a way to display values in a manner that doesn't necessarily conform with how it is stored. If you are joining on dates and times (which we have no way of knowing), I would consider changing your structures.

Duane
Hook'D on Access
MS Access MVP

 

[henniec]

Thank you for your replies.

Here is the sql.
SELECT Ozone1.SampleDate, Ozone1.SampleTime, Ozone1.CCW, Ozone1.Tempering, Ozone1.LPSW12, Ozone1.LPSW34, tblOzone.[ORP-1&2], tblOzone.[ORP-3&4], tblOzone.AuxSW
FROM tblOzone RIGHT JOIN Ozone1 ON tblOzone.SampleDate = Ozone1.SampleDate;

Date is formatted as dd/mm/yy, time as text hh:mm the rest as numbers.

It is an straight forward query that would normally work without a problem. As the query appears above the last three fields are blank. If I just reverse the link the last three are displayed and the first are blank.


dhookom. I do mention the fact that the date and times are the same as some of the data is imported from Excel and that data is extrated from a different system. I have had a lot of problems in the past especially when I came to time. The data is time stamped and when doing some extration it is not always true that it does not matter how it is formatted.

I have spend hours trying to short out one particulare query where time was a factor and having by only formatting a date, format dd/mm/yy hh:mm, formated as dd/mm/yy you cannot do averages on it. The data must be split from time. I formatted the time in Excel as hh:mm and imported. Looks correct but if you change the format in Access to dd/mm/yy hh:mm you will notice it is not hh:mm anymore but a date is added to it starting with the year 1900. Having time formatted as text hh:mm solved the problem.

Due to the above I am very wary when date and time is a factor as the same thing would happen by linking two tables as it is not compatable.

SkipVought: Will consider contributing.

Thanks.

Hennie

 

[dhookom]

You need to understand that date and time fields are floating point numbers. The date/time at this moment is about 40204.4990393519. The ".499" suggests it's almost noon. It doesn't make any difference how you format this value, it remains 40204.4990393519.

I can't imagine pulling data from more than one source and attempting to join on 40204.4990393519. You could try to round this value to increase the chances of finding a match.



Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

FYI,

Date/Time Values are just NUMBERS, like right now in North Texas the Date/Time value is 40204.49582.

That Date/Time value can be FORMATTED to appear any number of different ways WITHOUT CHANGE THE VALUE, like...
[tt]
Tue Jan 26
2010 January 26
26-01-10
1/26/2010 11:53
11:53:59 AM
ad nausium
[/tt]
Your 1900 date comment, is because the DATE part of the Date/Time value is ZERO.

Your frustration indicates that you really do not understand what a Data/Time value is. In a query, you must convert a string to a Date/Time value, using ## conversion delimiters. When you use a text string that is structured, dd/mm/yy , Access has to use its own rules to convert. Guess, what? Bill Gates, Seattle WA USA mm/dd/yy, trumps your dd/mm/yy. YOU wanna win? Then use the UNAMBIGUOUS yyyy/mm/dd structure for ALL YOUR DATE STRINGS.




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[henniec]

I do understand the conscept of the date. The fact remains if that if you need to do an average based on a day you cannot do it on the day value. The day value must be a whole number as the decimals change with every second. The same is true if you need to extract data on an hourly basis unless you separate the decimals from the number and select the hourly decimals using the date value would make it impossible. I used format dd/mm/yy as an example as myself don't appreciate the format yyyy/mm/dd.

Beside the above discussion it still don't solve my original problem about why would I only get half the data in a simple query. I will check tomorrow if it make any difference if I convert everything to numbers and run the query again.

Thank you for your input.

Hennie

 

[SkipVought]

Regarding your query, are you ABSOLUTELY sure that both tblOzone.SampleDate and Ozone1.SampleDate Date/Time Values are the INTEGER part only and contain no decimal component?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[henniec]

I change everything to numbers as I mentioned yesterday and it seems to work. Will in the future run the dates as numbers and only in the final query or report format it as a date.

Thank you for your input and our discussion around the date.

Hennie