Selecting dates in query

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;
LVL 4
Stewart_HendersonNO1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Select * FROM tblCrosstab050312;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stewart_HendersonNO1Author Commented:
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
peter57rCommented:
Do you not use the query design grid?
The query type is selectable once you have your basic select query defined.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Hamed NasrRetired IT ProfessionalCommented:
SELECT * INTO tableToMake
FROM CrossTabTable;
Stewart_HendersonNO1Author Commented:
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
peter57rCommented:
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.
Stewart_HendersonNO1Author Commented:
I used this answer and with some more work carried out by myself a solution has now been achieved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.