Novice User Help Please

[bmschaeffer]

They are mostly the same, but new ones could pop up every month

[Robeen]

I thought you might say that.

Then there has to be a Table [or Query] in your database that has a list of all the Vendors. Then you can use the complete Vendor list to create your report.

Can you be certain that all Vendors that existed in Jan - will still exist in Mar?
In other words, will your latest table have ALL the Vendors that you need to report on? Or is it possible that some will not be there from previous months?

How many months will you be having along the top?
On your report, you'll have to know how many months will be along the top.

And you'll have to change the Month headers each month?

Eg: you have Jan, Feb, Mar . . . the following month you will change your report design to include Jan, Feb, Mar, Apr?

[bmschaeffer]

I created a vendors list using a delete duplicated macro in excel. I have a list of all possible vendors from Jan-Mar, but there might be a vendor in Jan that is not in March and vice versa. I goal is to be able to run a YTD for actuals, so I could have 12 columns total, but i could have 2.

[Robeen]

Here's what I think you should consider if you HAVE to have the Months along the top of your report:

First: IF you're only reporting for the Current Calendar year ONLY:
When you get to January and are starting a new year, you might start a New 'MasterTable'.
You can Name the MasterTable for each previous year like 'MasterTableyyyy'.

Then:

1. Use Append Queries to put all your data into one 'MasterTable'.

2. Each month - use an Append Query to add the new data to the 'MasterTable'.

3. Create a 'QryVendors' that pulls each Vendor name from your 'MasterTable':

 
Select Distinct Vendor From 'MasterTable'

4. Write queries to pull 'Vendor' & 'Quantity' for each month that you will want to report on:

For 'CurrentMonth':

 
Select Vendor, Quantity 
From MasterTable
Where TransactionDate = Month(Date())

For 'PreviousMonth':

 
Select Vendor, Quantity 
From MasterTable
Where TransactionDate = Month(DateAdd("m",-1,Date()))

etc . . .

If you want to go with queries for each month [Jan, Feb, Mar . . .] of the current year, you'll have to approach it differently.


Eg:

'QryJan':

 
Select Vendor, Quantity 
From MasterTable
Where Year([TransactionDate]) = Year(Date()) 
And Format(([TransactionDate],"mm")=1

'QryFeb':

 
Select Vendor, Quantity 
From MasterTable
Where Year([TransactionDate]) = Year(Date()) 
And Format(([TransactionDate],"mm")=2

etc . . .

When you're done, you will have a query for each of the 12 months of the current [Calendar?] year that you want to report on.

Now you can create a Query that Has 'QryVendors' and your 12 'Month' queries [QryJan, QryFeb, QryMar . . .] in it.

Make LEFT joins between the Vendor in the 'QryVendors' & the Vendor of all your month queries. This will mean that for every month across your query there will be a row for EVERY Vendor in your MasterTable - even if there is no data for a particular Vendor in one or more months.

Then - Drag your Vendor from the 'QryVendors' into a query field - and then drag the Quantity from each of your month queries into a field in your query.

If you get the results you need - you can base your report on this last query.