Cross Check table (1 Viewer)

[Joy83]

Hi

I am trying to create a query but couldn’t figure out the logic.
I need your help

I have tow tables:

Table A:

Item

Start date

End date







Table B :

Item

Date (first date of each quarter)





I want to create a query to get me something like



Column heads are the list of first date of each quarter for the next 10 years



The data in the first column represent all the items from table A


Complete means: two things :

1- the item was active in this quarter (based on start and end date in Table A

2-the item for that quarter was found on Table A



Incomplete means: two things :

1- the item was active in this quarter (based on start and end date in Table A

2-the item for that quarter was NOT found on Table A



Not required means:

the item was not active in this quarter (based on start and end date in Table A





Example of output

Items	1-1-2023	1-4-2023	1-7-2023	1-10-2023
Item 1	Complete
Item 2	Incomplete
Item 3	Not required

 

[Mike Krailo]

My guess is you are trying to do something that you don't need to do. Finding the items per quarter can simply be calculated directly. You probably do not need a quarter table. What you want is items per quarter based on the example output. The perplexing thing to me is why you would need to look at future quarters that have no data in them? Now if you want to look at past data per quarter, that would make a little more sense. Are you sure you want to see a bunch of blank data for future quarters???

I had a system that created schedules in advance starting on every Monday and the form that created the schedules would show the user a combo box that populated the rowsource with every Monday into the future (calculated values). Now you can do the same with every quarter into the future as well. Then pressing the button on the form would generate the schedules for the next period as desired. Just something to think about.

 

[The_Doc_Man]

@Joy83 - Two comments:

First, to find a calendar quarter you can use DATEPART and the letter "q" for the "part" argument.

DatePart function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Second, now putting on my moderator hat, ... you reported Mike's response. I can't reply to you using that feature because you can't see the discussions that go on in the Reports area. If you have a problem, please explain it here or using a profile message to me.

 

[Joy83]

@Joy83 - Two comments:

First, to find a calendar quarter you can use DATEPART and the letter "q" for the "part" argument.

DatePart function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Second, now putting on my moderator hat, ... you reported Mike's response. I can't reply to you using that feature because you can't see the discussions that go on in the Reports area. If you have a problem, please explain it here or using a profile message to me.

sorry
I thought I replied

i was saying
The main challenge is that I don’t have an interface.
I just run the sql statement in a code.
I don’t have the list of quarter dates. That’s why I put future dates
My first table has a range of date
The second table not necessarily have the complete quarter date list.

What I was trying to do is to make sure that all the active items from table A
has a record in table B in each quarter they suppose to be active in

 

[The_Doc_Man]

OK, (Moderator hat on) You report was made in error. I am going to show it as resolved. (Moderator hat off.)

Your design is effectively a spreadsheet, which is immediately going to be an issue because Access isn't a spreadsheet program. Oh, it would easily make reports that say things you want said, but you might actually do better by thinking along the lines of Excel... unless there is a LOT more to this than you have described. Take my suggestion to mean "use the right tool for the right job" rather than anything disparaging.

Part of your issue is that you are doing something that sounds predictive. Your description of table B is, to me, a bit worrisome in that I don't see its logic yet, and you already suggested it might be only partly populated (which we call "sparse".)

 

[Joy83]

OK, (Moderator hat on) You report was made in error. I am going to show it as resolved. (Moderator hat off.)

Your design is effectively a spreadsheet, which is immediately going to be an issue because Access isn't a spreadsheet program. Oh, it would easily make reports that say things you want said, but you might actually do better by thinking along the lines of Excel... unless there is a LOT more to this than you have described. Take my suggestion to mean "use the right tool for the right job" rather than anything disparaging.

Part of your issue is that you are doing something that sounds predictive. Your description of table B is, to me, a bit worrisome in that I don't see its logic yet, and you already suggested it might be only partly populated (which we call "sparse".)

Thanks for your reply
Sorry that the problem was not explained properly

its not an excel function at all
Actually It was in excel and I moved it to a database for better results.
And it’s not a prediction . I don’t know where you got this.. if it’s the columns that contain future dates, if I can just list the dates up to date would be fine.

Here is more explanation and I hope you can help me once you know the logic..

Table B
Items are saved in this table (maintenance table)
If the item was maintained that quarter then it will be saved in this table.

i want to check what are the items that are active (from table A)
But not maintained (from table b)
But i want this report to segregate results by quarter.

 

[The_Doc_Man]

OK, this might not be a one-part result, but there is a thing called a "Find Unmatched" query which can be built by a query wizard. Search this forum for "FIND UNMATCHED" to see how to see what ISN'T represented in B to match items in A.

My advice is to take this in steps. A simple JOIN query between A and B might help you fill in the blanks for things that WERE maintained. The FIND UNMATCHED would then let you fill in the things not represented/matched.

The part about segregating by QUARTER requires you to look at cross-tab queries and use the DATEPART function mentioned in my earlier post as a way to determine the correct quarter. BUT you might want to do a little bit of a "think-ahead" here. When you consider table A, you can add two fields to show the starting quarter and ending quarter pre-calculated. I.e. rather than struggle to compute it later, have it in the table ahead of time? Save time and heartache later? Just a thought for your consideration.

 

[Joy83]

OK, this might not be a one-part result, but there is a thing called a "Find Unmatched" query which can be built by a query wizard. Search this forum for "FIND UNMATCHED" to see how to see what ISN'T represented in B to match items in A.

My advice is to take this in steps. A simple JOIN query between A and B might help you fill in the blanks for things that WERE maintained. The FIND UNMATCHED would then let you fill in the things not represented/matched.

The part about segregating by QUARTER requires you to look at cross-tab queries and use the DATEPART function mentioned in my earlier post as a way to determine the correct quarter. BUT you might want to do a little bit of a "think-ahead" here. When you consider table A, you can add two fields to show the starting quarter and ending quarter pre-calculated. I.e. rather than struggle to compute it later, have it in the table ahead of time? Save time and heartache later? Just a thought for your consideration.

Thanks a lot
Adding these two fields will fix my issue
I didn’t think about it

thanks again