Naming of tables/queries (1 Viewer)

[Twilkes]

Hi, I’m putting up my Access table/query names for critique, because at some point I’ll have to hand over this database to someone else and I want to see how they might react. So if you opened a query list and saw the list below, would you be able to find your way around, or are there too many similar looking queries?

Some of the queries are made up of 6 or 7 of the other queries – I find it’s easier to see what’s going on if they are separated out into individual queries, rather than throwing them all into a single select statement, and it means I can re-use things that I know work.

I get stuck on using plurals vs singular (e.g. StoresDeductions or StoreDeductions); how much detail to put in (e.g. BrokersChainsStoresCasesByMonth); how to indicate totals or being grouped by e.g. month; how to indicate date parameters, i.e. whenever a query says MonthRange it asks for this range, and any queries built on top of this one also have MonthRange in their name.

Any advice welcome. J

Accounts

[SIZE=3][FONT=Calibri]AccountsTotalNumStores[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainDeductionsMonthRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainDeductionsMonthRange_Crosstab[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainNumStores[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainsTotalsByMonthRange_Crosstab2[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainTotalsByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainTotalsByMonthRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainTotalsByMonthRange_Crosstab[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainTotalsMonthRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainTotalsPromoRatesMonthRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersChainTotalsPromoRatesMonthRange_Crosstab[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersStoreDeductions[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersStoreDeductionsGroupedByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersStoreDeductionsMonthRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersStoreTotalsByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]BrokersStoreTotalsMonthRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CasesPerActiveChainStorePerMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CasesPerMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]CategoriesTotalShipped[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Chains[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsCasesPerStorePerMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsCasesShippedByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsMonthOfFirstOrder[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsMonthOfLastOrder[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsMonthsNumStoresCasesShipped[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsStoresOrderedPerMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainStoresPerMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsTotalNumStores[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsTotalNumStoresByNDIAgent[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsToWarehousesBroker[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsToWarehousesNDIAgent[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsToWarehousesOrderedByBroker[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ChainsToWarehousesOrderedByNDIAgent[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsByAccountGroupedByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsByChain[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsByChainNoStoreID[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsByDistributor[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsByStore[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsByType[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsNonStore[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsNumFreeCaseFillsByDistRef[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsPlacementFeePerFreeCaseFill[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsPlacementFeesByDistRef[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsPlacementFeesProRata[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsStoreByBroker[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]DeductionsStoreGroupedByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MarketingByAccountByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoreOrdersByWarehouseByMonthRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresByMonthWithNumFreeCaseFills[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresCasesByProductByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresNotOrderedSinceDate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresTotalShippedByMonth[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresTotalShippedByProduct[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresWithFreeCaseFills[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresWithFreeCaseFillsByProduct[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]StoresWithFreeCaseFillsNoOrderData[/FONT][/SIZE]

 

[plog]

From a naming standpoint it looks good. I do have an issue with you relying on just the name to convey information to a new administrator (note I said administrator, not just a user). If you truly want to be a good developer you should document what you have done and what the objects of your database do. Either create a Word or Excel document to explain what each object of your database does and some helpful notes about how to use/modify it. Better still a table with at least two fields: one field would list the object name and the other would hold its description. That way the information stays with the database and can easily be updated by whomever comes next.

From a developers standpoint, I think you have a bunch of queries that can be made redundant and a bunch of queries that could be eliminated by creating a from/reporting system that works together.

 

[Twilkes]

...creating a from/reporting system that works together.

Could you describe how this would work, based on your understanding of what my queries do?

At the moment I have some final queries (tend to be the crosstab ones) that can get pasted straight into a spreadsheet template and sent off to whoever needs to see them; and also an Excel dashboard-type-thing which links to a couple of the queries, but also some pivot tables/charts based on a link to the consolidated raw data (which I don't think was in the query list).

 

[plog]

What I do is I create a form with input criteria at the top, a drop down that lists all the queries/reports available and then a 'Run' button. In the instance of your Month range queries I would set up the top portion to allow the user to enter their month range, they could then select the appropriate query/report from the drop down and then when they click the button the report/query would seemlessly open to the report/query they want showing just they data they selected.

Additionally, I'm sure there are opportunities to consolidate all the other queries that have the same underlying sources. Essentially, you would make your queries more generic and then have the Form/Reports do the grouping, sorting and even applying some criteria as described above.

 

[Twilkes]

Ah, form/reporting - your initial post said 'from'.

I think that's kind of what I'm doing with my Excel sheets - this is what the end users of the data will see, and there are only a few users in any case. It wouldn't be worth my time creating a form front end as I'm probably the only person who's ever going to open the database itself, apart from whoever takes it over in the future.

Thanks for the help,