Query logic for two different complex reports

[xboxown]

Hey guys,

I need to make two complex reports with complex queries that are beyond my abilities and skills so I am coming here for your help (I am attaching two images that shows what the report I am looking for). The first report displays the GL of the customers and date value 1, date value 2, date value 3, date value 4 and so on are GLs based on that year/month/date selected and the total amount of price for that GL of that year/month/date. Look at picture 1 for more details.


Picture 1


Picture 2

The second report display Sales Rep, Company Name, Date year vs Date year. This report is comparing company name of that year with another company name of a second year. I was doing two queries and this how it was coming out:



Query 1 (Year 2016):

• Company A =======> Sum_Total($3000)
• Company B =======> Sum_Total($123.34)
• Company C =======> Sum_Total(456.89)
• Company D =======> Sum_Total($1500.34)
• Company E =======> Sum_Total($223.34)
• Company F =======> Sum_Total($13.45)

Query 2 (Year 2017:

• Company A =======> Sum_Total($30)
• Company B =======> Sum_Total($5000)
• Company C =======> Sum_Total($1000)
• Company D =======> Sum_Total($0)
• Company E =======> Sum_Total($450.90)
• Company F =======> Sum_Total($79.99)
• Company G =======> Sum_Total($40)
• Company H =======> Sum_Total($9000)
• Company I =======> Sum_Total($4.99)

The Report will Display it like this

Company Name	2016	2017
Company A	Sum($3000)	Sum($30)
Company B	Sum($123.34)	Sum($5000)
Company C	Sum(456.89)	Sum($1000)
Company D	Sum($1500.34)	Sum($0)
Company E	Sum($223.34)	Sum($450.90)
Company F	Sum($13.45)	Sum($79.99)
Company G		Sum($9000)
Company H		Sum($400)
Company I		Sum($199.99)

I have the query for report 2 working but the problem gluing them together is an issue. When I glue them together it doesn't display all the report, some doesn't show but if I run the query alone it displays all the record. How do I resolve this issue?

Thanks in advance.

[June7]

You say report includes sales rep yet that is not shown in the last example. Is the sales rep necessary? Will each company have only one sales rep?

Do you have a table of all companies? Try joining both queries to that table.

[xboxown]

The second report it filters all companies for that specific sales rep. I didn't put it in the example because I forgot to put it there but yes, sales rep is necessary. Like I said before joining query is what breaks it. If I run queries separately it displays correct data but how do I join them together without having invalid data?

Remember the logic of SQL access as is that both queries must have matching ID in order for them to join and appear and that becomes a problem here. These are the join relations in Access SQL:

Table A ................. Table B <OR>
Table A <............... Table B <OR>
Table A ................>Table B

I was hoping there is something like t his

Table A <..............> Table B

Or have it like this

Table A Table B

no connection between the table so both display their own query

or have two separate report and a third report where it links both reports without having any relations or something. I am baffled in how to approach this. This is why I am coming here, you guys are my last resort here. Can someone help me please? PLEASE!

[June7]

You want to limit the report to one sales rep?

If data is grouped by sales rep and company in both queries, then likely need a dataset of all salesrep/companies then join both queries to that dataset with compound join on both salesrep and company.

If you want to provide db for analysis, follow instructions at bottom of my post.