Advice on Database Structure

[Nevsky78]

Hi all,

I'm currently working out how best to structure a database I want to build and I'd like some advice from the experts!

My database will be built on 2 core objects:

Table - tblCustomers
Query - qryItems

tblCustomers will be manually updated and changed within the database
qryItems is a query which filters items from 3 linked tables which are in an SQL Database, based on 2 criteria (I only have one criteria in the example, but you get the idea)

I have attached an example with sqltbl prefixing those tables which will be SQL Server linked tables

The next stage is where it gets a little difficult.

There needs to be some kind of table/form where end users can input a number against 'fixed' months for each item and customer variable. The months, for example, will go something like this - Jan 2011, Feb 2011 etc., Jan 2012, Feb 2012 etc.

The end user will only want to see the items and months form for one customer at a time. They need to see the full grid of month options as well, rather than input month, quantity and item each time, as below (I've just included a selected range of months). They will also need to have the option to filter the view by the year.

Customer: Customer B

Item Oct 11 Nov 11 Dec 11 Jan 12 Feb 12 Mar 12 Apr 12 May 12
Product B
Product C
Product D
Product E
Product F

Any ideas?!

Thanks,
Nick

 

[Nevsky78]

Hi all,

I've updated the example table with tblMonths and tblYears.

I think I would like to have a 'standard' form showing 2011 Months (filtering by customer) and a 'standard' form showing 2012 Months (filtering by customer) and so on.

Nick

 

[Nevsky78]

Another update!

I realised I didn't actually need that qryItems - I just apply the filter on the full query/form, as per that attached database.

As you can see, I get the error about Outer Joins (I think around the tblCustomers - tblCustomerItem join).

This updated database should give a much better view on the core things I'm trying to achieve.

Nick