Timesheet access Project

[aucho22]

After reading some pieces about joins and others, I believe I understand there is something missing (a join) in my db, as you said.

Far from being an expert but seems like it is creating a "Cartesian product"... which causes the query to return unexpected results (too many records).

Answers I am finding on the web advise to create the joins between the tables, but I have only one table...? (see attached pic)

Does it mean I need somehow to create a second table? Sorry for the bad access language, I am all new to this.

Thank you,
Aucho22

 

[GinaWhipp]

Hmm, okay can you upload your database? Strip out the information, I just need the queries and the one table. Beleive me, this is easier than all your reading implies, it's just unfamilar to you.

 

[aucho22]

Here it is... Sorry for probably breaking some of the 10 commandments, this is actually the first one I do

 

[GinaWhipp]

Well, we'll just have a look...

 

[GinaWhipp]

Had a look and will make the necessary changes to make this work. Going to take a little bit because I see you have Forms so please bear with me... I do have a day job!

 

[aucho22]

Thank you so much GinaWhipp, I would try and do it myself if only I could understand what the core problem was

As you said, it is surely simple and I will probably learn some things from this first issue...

 

[GinaWhipp]

Hope you're reading...

Do Employees have more than one Role?

 

[aucho22]

Tricky one!
They may endorse different roles during the week (ie D1:Supplyman D2:Area Leader).
My thought process was that they input one role per entry.
If your question is "Can they have two different roles at the same Date/Time", then no, they cannot.

 

[GinaWhipp]

Nope, my question was as I posted, just fixing the Tables but that additional information you supplied...

They may endorse different roles during the week (ie D1:Supplyman D2:Area Leader).

...is helpful to how to set up the Tables.

 

[aucho22]

Alright

You did mention some days ago that the problem came from my tables...

 

[GinaWhipp]

Update! Tables done now moving on to Forms which will also adjust the queries.

 

[aucho22]

Cant wait to see what was wrong in my table design

 

[GinaWhipp]

Oh, table design done working on Forms, attached is a peek...

 

[GinaWhipp]

Oh, queries not working yet but I'm getting there!

 

[GinaWhipp]

Okay, here we go. Have a look at *qxtbEmployeeHours* and tell me if this is what you want.

 

[aucho22]

Wow, it definitely looks more like what I intended to do at first, thank you

Only thing, when creating the report I wanted the user to be able to enter the date range he/she wanted the report for. I thought I would just need to add a criteria in the query for WorkDate: Between [What date?] And [What date?], but it is not working, maybe because this field is an expression WorkDate: Format([tsDate];"m/j/aaaa") ?

Otherwise, it seems perfect, I just need to get my head around the new tables there are and their interaction with the queries...but that's my beginner brain.

 

[GinaWhipp]

So you want the crosstab query to feed a report that allows parameters?

EDIT: And you're welcome!

 

[aucho22]

Yes for this query "qxtbEmployeeHours", I will eventually create a report (the user will have to choose the date range)...

I noticed this query was more of a matrix, so not sure if it is possible?

 

[GinaWhipp]

It's possible... after dinner

 

[GinaWhipp]

Here you go...