Combo box for fields in table and form 2

[MSealy]

HI. I wonder if you can help. I'm creating a filing system database for box files of invoices. For my table 'tblINVOICES', I have the following fields:

Name Datatype Description
FILE REF: Number The file box number
PERIOD: Text - lookup The financial year (eg. 2008-09)
OPENED: Text - lookup File opened on the start of the financial year (eg. 01/04/2008)
CLOSED: Text - lookup File closed on the end of the financial year (eg. 31/03/2009)
INCLUDES: Number First invoice number in the current box
to: Number Last invoice in the current box.

Now as the financial year data will always be consistent, I have chosen a lookup for PERIOD,OPENED, and CLOSED. This is called 'tblPERIODS'. This table has the following fields and records..

PERIOD: Start Date: End Date:
2008-09 01/04/2008 31/03/2009
2009-10 01/04/2009 31/03/2010
2010-11 01/04/2010 31/03/2011 and so on.

So far this sounds ok, but I'm having problems with the my form, 'frmINVOICES' as I have all the fields from 'tblINVOICES' in there, but I'm trying to set a combo box on 'PERIOD:' which should populate the other two fields - 'OPENED:' and 'CLOSED:'. Eg. I select 2008-09 in 'PERIOD:' and "01/04/2008" should appear in 'OPENED:' etc. but I can't get it to work that way.

Am I going about this in the right way for data normalisation or should I be storing the data differently ? Do I just need one field in the 'tblINVOICES' for the combo box for instance ?

Any help is appreciated thanks.

 

[lameid]

I guess what you want to do depends on whether you can just store the period and trust the start or end date is related correctly or if you want to allow for one-offs and store the data.

In the first scenario you could have a Foreign Key to the Periods table, say Period_ID. You could then have multiple combo boxes bound to the Period_id field, each displaying different information. I would guess you would disable updates on start and end date in this scenario.

Alternately you could use the column property to return the different values programatically and use them to populate the other fields. I'm guessing you want the first method so I'm not going to go into specifics unless you need help.

 

[fneily]

You mentioned normalization - don't know if you read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

In tblInvoices, is FileRef your primary key? Should be. Good habit to use underscore, by the way so File_Ref.

Since Opened and Closed is the same as StartDate and EndDate, you don't need them in tblInvoices. Violates normalization protocols.
So tblInvoices will connect to tblPeriods through Period, which, again, should be the primary key for tblPeriods.

So, you only need one combobox on your form for Period.

Have a question about Includes. Is this a field that looks like this 1 - 500? Then split it into two fields, Beginning and Ending. Easier to change, code, run logic on, etc.

 

[MSealy]

Thanks. I have a bit of a problem with the primary key though. File ref would be ideal, but this is not possible, as I will have file refs say 1-10 for 2008-09 and another set of 1-10 or so for 2009-10.

'Includes:' is a a starting number and 'to:' an ending number. Fortunately, this part is setup the way I need it.

 

[lameid]

From a filing stand point and finding boxes, I agree that the box numbers should be unique. If they are unique, they can be stored sequentially or a map created to find them. In the long run this could save you a lot of trouble.

Now that I have said that, if you really want to manage your boxes that way consider the following but disregard it otherwise...
From a data perspective you could simply add an autonumber field to be the primary key. You could also use the File_Ref and Period_ID as a composite primary key. Even if you use an autonumber primary key it sounds like you should have a unique key over those two fields.

 

[lespaul]

Ok so when is the data going to be different in the two locations? Will you ever record a period in the tblInvoices that's different from tblPeriods? Then you are storing duplicate data...you should either have tblInvoices just like it is and drop tblPeriods OR remove Opened and Closed from tblInvoices and add an Autoincrementing ID in tblPeriod and put THAT in tblInvoices:

[tt]
tblPeriods
ID PERIOD: Start Date: End Date:
1 2008-09 01/04/2008 31/03/2009
2 2009-10 01/04/2009 31/03/2010
3 2010-11 01/04/2010 31/03/2011

tblInvoices
FILE REF: Number The file box number
PERIODID: Number FK to tblPeriods ID field
INCLUDES: Number First invoice number in the current box
to: Number Last invoice in the current box.[/tt]

now if you need to know the period or dates then you JOIN into tblPeriods:

SELECT * from tblInvoices
INNER JOIN tblPeriods on tblInvoices.PeriodID = tblPeriods.ID

now you have ALL the fields from BOTH tables....


Leslie

Have you met Hardy Heron?

 

[MSealy]

Thanks. Before I give it a try, these are the sort of records I'll end up with in tblINVOICES:

File Ref: Period: Opened: Closed:
1 2008-09 01/04/08 31/04/09
2 2008-09 01/04/08 31/04/09
3 2008-09 01/04/08 31/04/09
1 2009-10 01/04/09 31/04/10
2 2009-10 01/04/09 31/04/10
3 2009-10 01/04/09 31/04/10
4 2009-10 01/04/09 31/04/10

So basically, Period, Opened & Closed will always relate to the same dates. It is nice to have these as separate fields for text boxes on a report though - rather than one field in a combo box.

I've never used JOIN before. Is this part of a query ?

 

[MSealy]

Oops I left out the other data. It will actually be more like this

File
Ref: Period: Opened: Closed: Includes: to:
1 2008-09 01/04/08 31/04/09 1 347
2 2008-09 01/04/08 31/04/09 348 596
3 2008-09 01/04/08 31/04/09 597 840
1 2009-10 01/04/09 31/04/10 841 992
2 2009-10 01/04/09 31/04/10 993 1060
3 2009-10 01/04/09 31/04/10 1061 1345
4 2009-10 01/04/09 31/04/10 1346 1678

 

[lameid]

No... The sort of records you end up in tblInvoice will be like...


File
Ref: Period_ID: Includes: to:
1 1 1 347
2 1 348 596
3 1 597 840
1 2 841 992
2 2 993 1060
3 2 1061 1345
4 2 1346 1678

When you run the query and join everything together you can end up with a data list as you made.

 

[lespaul]

You're missing the point....you only store a single piece of information in ONE place. With the schema you first posted above you have:

PERIOD in BOTH tables
START/OPENED - the SAME date in BOTH tables
END/CLOSED - the SAME date in BOTH tables

Pick a table to put that information in and put it in ONCE.

So you can have this where all the information is about a BOX. It shows the time period of information IN the box and the range of Invoices IN the box:
[tt]
tblInvoices
Name Datatype Description
FILE REF: Number The file box number
PERIOD: Text - lookup The financial year (eg. 2008-09)
OPENED: Text - lookup File opened on the start of the financial year (eg. 01/04/2008)
CLOSED: Text - lookup File closed on the end of the financial year (eg. 31/03/2009)
INCLUDES: Number First invoice number in the current box
to: Number Last invoice in the current box.
[/tt]

OR you can have this:
[tt]
tblPeriods (All about the time Periods that the BOX will reference)
ID PERIOD: Start Date: End Date:
1 2008-09 01/04/2008 31/03/2009
2 2009-10 01/04/2009 31/03/2010
3 2010-11 01/04/2010 31/03/2011
[/tt]
AND another table that shows the what's in the box and REFERENCES tblPeriod to determine the TIME range of the box:
[tt]
tblInvoices
FILE REF: PERIODID: INCLUDES: to:
B12345 1 1001 12505
B12346 1 12506 25601[/tt]

So the Period information is stored ONCE in the period table. If you need to know the start and end date then in a query:

SELECT FileRef, Period, [Start Date], [End Date], [INCLUDES:], [to:] FROM tblInvoices
INNER JOIN tblPeriod ON tblInvoices.PeriodID = tblPeriod.ID

will return:
[tt]
FileRef Period [Start Date] [End Date] [INCLUDES:] [to:]
B12345 2008-09 01/04/2008 31/03/2009 1001 12505
B12346 2008-09 01/04/2008 31/03/2009 12506 25601[/tt]

The key concept here is that you only put the data in your tables ONCE...Let's say you have a employee file that contains:
[tt]
ID FName LName
1 Joe Blow
2 Jane Doe[/tt]

if you have another table that records bonus that person has received, you would have something like:
[tt]
BonusID BonusDate BonusAmt EmployeeID
1 1/1/2008 100 1
2 1/1/2008 200 2[/tt]

You can see that we can JOIN into the Employee table to get the names if we need them. If we were to structure the bonus table like you are trying to structure your Invoices table it would look like this:
[tt]
BonusID BonusDate BonusAmt EmployeeID FName LName
1 1/1/2008 100 1 Joe Blow
2 1/1/2008 200 2 Jane Doe[/tt]

Doesn't make any sense to put the persons' NAME in two places...that's what you are doing by putting PERIOD, START/OPENED and END/FINISHED into two places....






Leslie

Have you met Hardy Heron?

 

[MSealy]

Thanks for this. I've decided to changed my tables to how you suggested. In order to deconfuse things when dealing with code, I've removed spaces and colons in my fields.

tblPERIODS now has
ID - Autonumber (Primary Key)
StartDate - Date/Time
EndDate - Date/Time

tblINVOICES
FileRef - Number
PeriodID - Text
Includes - Number
to - Number

How should I join these tables? Does tblINVOICES need a primary key, and should I do this in the relationship window or the lookup field wizard on the table designer?

I found the query didn't work (I did rename the fields where needed) but maybe this is also to do with the relationship not set up right.

 

[MSealy]

Right, I've now used the lookup wizard in tblINVOICES to match to tblPERIODS' ID field.

Then I modified your query to ..

SELECT [FileRef], [Period], [StartDate], [EndDate], [Includes], [to]
FROM tblInvoices INNER JOIN tblPERIODS ON tblInvoices.PeriodID=tblPeriods.ID;

and called it qryfrmINVOICES

and based my form frmINVOICES on qryfrmINVOICES.

Now it shows the data I want, but I'm back to the combo box problem again.

 

[fneily]

Check out:
The Evils of Lookup Fields in Tables

http://www.mvps.org/access/lookupfields.htm

In your tblPeriod, you have just ID. In the tblInvoices, you have PeriodID. You should keep field names spelled the same. It's easier for Access to know that they are equal and anyone maintaining your database.

Also, you have ID has a number and PeriodID as text. This is incorrect.

"Does tblINVOICES need a primary key". ALL tables should have a primary key. No matter what anyone says. See Codd's theory.

"Now it shows the data I want, but I'm back to the combo box problem again." No. You are not storing any Open and Close dates. They are all ready in the table(tblPeriods)

Post your combobox code(rowsource). And state your problem clearer.

 

[PHV]

Provided the relationships are correctly defined you'll simply follow the combo wizard.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

and based my form frmINVOICES on qryfrmINVOICES.

No, base your form on tblInvoices and reports to show the information on qryfrmINVOICES (I would name it more generically like qryInvoices).

Then go back to my original post...

In the first scenario you could have a Foreign Key to the Periods table, say Period_ID. You could then have multiple combo boxes bound to the Period_id field, each displaying different information. I would guess you would disable updates on start and end date in this scenario.

You used PeriodID instead of Period_id which is fine.

PHV is right in that the combobox wizard should make things easy. I've had hit and miss luck with things that are not explicitly installed. That would be the advanced Wizards for Access if it does not pop up a wizard when you add a combobox to your form.

When I said disable updates you can do this by setting the locked property of the affected combo boxes to true / yes.

 

[MSealy]

I now remember why I used to just live with duplicated data. I've never used foreign keys before.

I've now based my form on tblINVOICES, but when doing the combo box wizard, I get confused with the options to remember the value for later use, or store in a field - as the only options for storing the data are the fields in tblINVOICES. But tblINVOICES only has the other half of the data and if I store it in a new field there I'm going in circles by duplicating the data from tblPERIODS in tblINVOICES.

I know I'm almost there, the tables and the query are working but I'm just having difficulty getting all the info on the form and with combo boxes.

 

[lameid]

The correct option is to store in a field as you are storing the foreign key in the table. The combobox will then display the data for the display column you select to display (hide the key column). There is nothing preventing binding multiple controls to the same field. So you can have multiple combo boxes bound to PeriodID, each having a value you can see on screen.

 

[MSealy]

Thanks. I already had an ID field in tblPERIODS. I then created 3 combo boxes on my form (based on tblINVOICES), each storing the value in the ID field of tblPERIODS. I thought it would mess up my ID numbers, but somehow this works. Great.

I now have what I want - 3 combo boxes which change when one item is changed. All I need to do now is to remove the arrow graphic of the last 2 and lock them so that only combo box 1 is selectable.

 

[lameid]

You can't remove the arrow graphic from a combobox.

You can use text boxes instead... You make their control sources look at a particular column value of a combobox.

If your combobox is named cboPeriod...

=cboPeriod.Column(1)

The above is the control source for a text box that shows the second column. Columns are start at 0 and go up, even though the bound property starts with 1.

To use this method you have to include all the fields you want to see in the combobox and obviously indicate the correct column with the number. If the screen layout allows it, I like to line up the text boxes next to the combobox so it looks like all the text boxes are columns in it. That is a nice effect but not maybe not what you are after here.