Lookup a field from another table?

[yeleek]

Hi,



I have a query which is collecting the number of alerts per weeknumber for a given month, i'm then using this in a report to display the results.

So the report may show

Week 19 20 alerts

I've another table which has 3 columns, weeknumber, year and weekstart. This is fully populated (via excel) with weeknumber, year and weekstart until 2015.

The idea being for the original report I would like it to be able to say week 19, 20 alerts, and then retrieve the weekstart from the 2nd table (it knows the year as that is part of the criteria). Is this possible and if so how please?

Thank you in advance

[jzwp11]

On what table(s) is the query based? What are the fields in the query? You may be able to create a query that joins your first query with the table you mention and then you would have access to all fields in both including the weekstart.

[yeleek]

Hi the query looks like above, and as I say I've another table which has 3 fields, week number, year and weekcommencing. What I'd like is that for each week number in a month (as per criteria) it pulls the weekcommencing from the other table. Is that possible? Thanks

[yeleek]

Alternatively is there a calculated query field i can create which given the [Week] and [year] can work out the date of the monday?

Thanks in advance

[jzwp11]

You should be able to create a query that includes the query you mention and the table. You would join the two via the week and displayyear in the query to their respective fields ([week number] and [year]) of the table.

[yeleek]

Week is a calculated field in this query, can you join that to a field on another table via a relationship?

I ask as I've tried via relationship, and then running the query and access just sits there. Saying its running the query.

[jzwp11]

Yes, you should be able to do that.