Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Selecting dates in query

Posted on 2012-03-21
7
Medium Priority
?
175 Views
Last Modified: 2012-04-18
Hi,

Each day data is added to a table, which I have extracted in a crosstab query in the format below -

I then link this data below to another query, but how can I automatically include all the days in this new query without adding them manually each day

first_name surname emp_no      From 0500312    19/03/2012
David        Franklin        WO055      2030      316      59.75      

Below is the sql from the new query - how can this be modified to include all dates from 050312, currently it only shows up to the 19th but the 20th is already in the crosstab table.

SELECT tblCrosstab050312.first_name, tblCrosstab050312.surname, tblCrosstab050312.emp_no, tblCrosstab050312.Allocated_Hrs, tblCrosstab050312.Hrs_Jan_Mar, tblCrosstab050312.[Total Hrs From 050312], tblCrosstab050312.Remaining_Hrs, tblCrosstab050312.[19/03/2012], tblCrosstab050312.[16/03/2012], tblCrosstab050312.[15/03/2012], tblCrosstab050312.[14/03/2012], tblCrosstab050312.[13/03/2012], tblCrosstab050312.[12/03/2012], tblCrosstab050312.[09/03/2012], tblCrosstab050312.[08/03/2012], tblCrosstab050312.[07/03/2012], tblCrosstab050312.[06/03/2012], tblCrosstab050312.[05/03/2012]
FROM tblCrosstab050312;
0
Comment
Question by:Stewart_HendersonNO1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1500 total points
ID: 37748042
Select * FROM tblCrosstab050312;
0
 
LVL 4

Author Comment

by:Stewart_HendersonNO1
ID: 37748265
Hi,

This syntax below was previously a make table query, now using the Select * gets me all the dates but how can I also make it a make table query  (tblCrosstab050312)


SELECT *
FROM qryEngineeringHrs050312_Crosstab INNER JOIN tblEngineersAllocatedHrs ON qryEngineeringHrs050312_Crosstab.emp_no = tblEngineersAllocatedHrs.emp_no;

Thanks
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37748284
Do you not use the query design grid?
The query type is selectable once you have your basic select query defined.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Expert Comment

by:hnasr
ID: 37748664
SELECT * INTO tableToMake
FROM CrossTabTable;
0
 
LVL 4

Author Comment

by:Stewart_HendersonNO1
ID: 37751802
Hi,

My issue all along has been how I can get specific data from a tbl and have it arranged in a specifc order, if it wasn't for the fact a new date is added to the original table each day then I could do it via the design view comfortably.

I have enclosed the tbl in question, I wish to extract in a query in the following order -

First Name, Surname, Emp_No, Alloc_Hrs, Hrs_Jan_Mar,Total Hrs, Remaining, then all the dates in descending order

As mentioned each day a new date is added to the table and I do not want to manually add this to the query (just selecting * will not give me the sort order)

Help much appreciated
Database2.mdb
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37756338
OK so the nub of the issue is field order.
I that case I can see that you have a problem.  I can't see any simple change to get what you want.

I think you will have to build the select query in code.

It seems to me that you would have to get a list of distinct date field values from the table, and from that build a list of field names which can be used to build a complete sql query to select from the crosstab query.
0
 
LVL 4

Author Closing Comment

by:Stewart_HendersonNO1
ID: 37859920
I used this answer and with some more work carried out by myself a solution has now been achieved.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question