Access Q&A: Unmatched, OpenReport, Time
By Richard Rost
19 years ago
More recently answered Access questions...
Hi, Richard:
I have two tables in access (let's name them Table A and Table B). records in Table B is subset of Table A. I need to get records in table A, which is not in Table B.
I don't know how to use unmatched query.
Thank you in advance. Anna
Anna, that is kind of what the Find Unmatched Query Wizard does... it looks at the records in one table that are NOT found in another table. You generally use it to say "show me all of the customers who don't have orders" or "all of the cars that are missing drivers" but you could use it when you have two tables and you want to see which items are missing from one table - as long as you have one field that is identical (an ID or something else you can use the join them).
The wizard is pretty easy to understand, so you shouldn't need step-by-step instructions. If you really can't figure it out, I do cover it in detail in my Access 223 tutorial.
while doing reports - my program will open a query. The thing is once the query is closed, the report should open automatically for preview. My question is how do i find out if the query has been closed and command the report to open. Mohan.
Mohan, how are you doing it now? It's really hard to attach EVENTS to queries. They don't have any VBA code or other event handlers attached to them.
Here's what I would do: take your query and build a FORM based on it. Put the form in Datasheet view so it looks like a query. Then, in the OnClose event for the Form, you can open your report with a little DoCmd.OpenReport command... or just stuff it in a command button.
I cover DoCmd.OpenReport in my Access 304 tutorial.
I'm running a query on a table with 6000 records. The query should return about 4500 lines because I'm excluding a certain region. The funny thing now is that access return about 2 billion records running this query. How is this possible? Harvey.
Harvey, it sounds to me like you might have accidentally created a Cartesian Product with your query. Check to make sure you haven't accidentally put two unjoined tables in your query.
This would happen, for example, if you had two tables with Students and Tests, and you wanted to list every student with every test. Essentially it's a 'product' of the two tables.
Delete the second table - or create a JOIN between them - and the product goes away.
I talk about this in my Access 223 class.
In fact, the only way I remembered what it was called was because I searched my own web site for it. Ha ha ha.
Background: I have written db to measure press productivity and efficency. Reports display past 24 hours of data begining with Day shift. For Example, a report reviewed on Monday morning would include Friday A & B shift data and Sunday C Shift data. Tuesday mornings report would include Monday A&B shift and Tuesday C shift. Information needs to be retrieved for the current and previous months.
Question: How do I set criteria to retrieve this data?
I was using Date()-1 or Date()-2, etc. This worked as long as the workweek begins with C shift on Sunday and ends with B shift on Friday. It does not, However, when it begins with Day shift Monday and ends with C Sunday. Including Sunday C with the previous Friday A & B is throwing me. Kelley
If I understand your problem, you need to be able to generate a report based on fractions of a day... not just whole days. Access treats days as whole units, so one hour is 1/24 of a day.
If you need to generate a query showing all of the data from midnight to 12 noon today, you would say
Between Date and Date+(0.5)
So, if you need to generate a report showing last night's work (let's say after 8pm) and all of today's work until 3pm, you would say:
Between (Date-1+(20/24)) and (Date+(15/24))
Remember, Access thinks in 24hr time, so 8pm = 20:00 hrs; 3pm = 15:00 hrs. Yes, you can simplify the fractions, but it makes it easier to read if you don't.
Now, you should be able to take this into consideration to fix your problem. Unless I'm misunderstanding your question, this should help. If not (if I'm off base) please explain how your shifts work in more detail.
I cover how Access deals with time in more detail in my Access 221 tutorial.
Hi,
I am a basic Access designer and need a basic answer please:
I have a table with tasks in it.
Each task has a due date.
The date could be for example Nov 3 07 8:58:19 AM
In a query, I would like to display all records that have a date that is less than or equal to today.
I have been using the following criteria in the [Due date]field:
<=Now() Or Is Null
I would like to display all records if they are due today, regardless of the time of day.
I find this hard to explain so please allow me to use an example:
1) Say the current time is Nov 3 07 9:00:00 AM
2) Say the due date of a record is Nov 3 07 11:00:00 AM
3) I would like the record displayed becuase it's date is less than or equal to today
4) BUT because the record's time is LATER than the current time, it does not get displayed.
5) In other words, I would like to display a record if the date portion is <= today, regardless of the time portion.
I have tried DatePart function to no success.
Help would be much appreciated. Joseph.
Read what I sent to the previous gentleman about how Access handles dates and times.
I would just make your criteria for your DueDate field:
Between Date and Date+1
This says, "show me everything due from 12:00:01 this morning to 12:00:00 tomorrow morning."
If you want to include everything in the past up to and including everything today, say:
< Date+1
Easy enough?
|
