Combining Data Results from Multiple Queries (1 Viewer)

[WillM]

Hello folks, hope all is well.

I am working on a report that has some special characteristics and I need some help trying to figure it out. The explanation is a bit long, so I apologize in advance.

Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.

Each of these tables has a date that the Complaint, Complement, and Termination notice was received.

Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.

The equation used for that is:
(complaints/vendors_in_group)*100

It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.

What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.

Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.

2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.

3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work. Am I looking in the right area or should I try something else? I am completely open to suggestion, as I am nearing my wits end with this.

Thank you for your time!
Will

 

[plog]

I need to be able to store the information attached to the fiscal year and month

You are already storing this data at a more granular level (by date), there is no need to store it at the month/year level. It sounds like all you need is a query not an entirely new table (or set of tables).

Also, you've described 2 fields in Terminations, Complaints and Complements (VendorID, Date). What other fields do they have? Specifically do they have the exact same structure?

 

[WillM]

I am not sure I understand why I wouldn't need the month/year, since the query is looking at rolling quarters, and I have to have the previous 4 quarters when the report is run. I am not an expert, but it seems like that would require changing the query every time I need to run the report and move the parameters of the date to coincide with the quarters needed. I would like to have this automated, thus the bigger issues I am getting into, as this is a report that other people will run once it is finished.

The other tables have a lot of fields in them, and the structure is solid throughout.

 

[plog]

There's no reason to store redundant data. You wouldn't store the fiscal month/quarter/year, you wouldcalculate it when you need it. In a query you can take a date and calculate the fiscal month/quarter/year it occurs in.

As for your look backwards 4 quarters, that can be accomplished with a WHERE clause. Since 4 quarters = 1 year, your criteria for the previous 4 quarters would be:

>DateAdd("yyyy", -1, Date())

The other tables have a lot of fields in them, and the structure is solid throughout.

That's like a kiss of death on this site. About 90% of the time that is said, the structure has serious flaws. Can you post your relationship screen?

 

[WillM]

Unfortunately, I can't, I apologize.

Our fiscal year runs from Sept 1st to Aug 31st, and I have to have the information broken down by quarter.

I will keep working at it, thank you for your response!