Please help, i"m very confused about tables & queries!

Hi,

I'm probably being quite stupid but I can't see the wood for the
trees!

I have a table (tblimport) with about 1300 records. The table has a
field (region) with a value from A to Z. (So there will be about 50
records for each region letter). These are pre-set when the data in
the table is imported in.

I need a user to be able to assign each unique region a number from 1
to 6. (So there would be about 4 or 5 regions to each number).

I would like the method for this to be;

User opens a form. Form shows a list of all unique regions (A to Z)
and can then assign each one a number (1 to 6). The regions and
assigned numbers are stored in a table. (I will then be able to query
the tblimport and the new table to show all the records from tblimport
with an assigned number)

I simply cannot see how to do this! I'd appreciate somebody setting my
brain straight. Thanks,

Re: Please help, i"m very confused about tables & queries!

You need three tables:

TblRegionLetter
RegionLetterID
RegionLetter

TblRegionNunber
RegionNumberID
RegionNumber

TblRegion
RegionID
RegionName
RegionLetterID
RegionNumberID


In TblRegionLetter you should have 26 records (A to Z). In TblRegionNumber
you should have 6 records (1 to 6). You need a form named FrmRegion based on
TblRegion. FrmRegion needs a combobox based on TblRegionLetter to enter
RegionLetterID and a combobox based on TblRegionNumber to enter
RegionNumberID.

Steve

"keri" wrote in message
news:779d57c1-9bdc-449a-b2c6-f1c5204ed003@a5g2000prg.googleg roups.com...
> Hi,
>
> I'm probably being quite stupid but I can't see the wood for the
> trees!
>
> I have a table (tblimport) with about 1300 records. The table has a
> field (region) with a value from A to Z. (So there will be about 50
> records for each region letter). These are pre-set when the data in
> the table is imported in.
>
> I need a user to be able to assign each unique region a number from 1
> to 6. (So there would be about 4 or 5 regions to each number).
>
> I would like the method for this to be;
>
> User opens a form. Form shows a list of all unique regions (A to Z)
> and can then assign each one a number (1 to 6). The regions and
> assigned numbers are stored in a table. (I will then be able to query
> the tblimport and the new table to show all the records from tblimport
> with an assigned number)
>
> I simply cannot see how to do this! I'd appreciate somebody setting my
> brain straight. Thanks,

Re: Please help, i"m very confused about tables & queries!

Thanks. My concern with this is that all of the letter records are not
listed in one view on the form, thereby not forcing the user to enter
a number for each letter. (Plus being able to see the list of all
regions will make it much easier for the user to assign a number). Is
there any way to solve this?

(As you may have guessed i've used letters as an example, it would
actually be postcodes so the user would not know what they all were
without being able to see them on the form)

Re: Please help, i"m very confused about tables & queries!

keri wrote:

> Hi,
>
> I'm probably being quite stupid but I can't see the wood for the
> trees!
>
> I have a table (tblimport) with about 1300 records. The table has a
> field (region) with a value from A to Z. (So there will be about 50
> records for each region letter). These are pre-set when the data in
> the table is imported in.

Have you added a region to the table?
>
> I need a user to be able to assign each unique region a number from 1
> to 6. (So there would be about 4 or 5 regions to each number).

Have you added a region number field to the table?
>
> I would like the method for this to be;
>
> User opens a form. Form shows a list of all unique regions (A to Z)
> and can then assign each one a number (1 to 6). The regions and
> assigned numbers are stored in a table. (I will then be able to query
> the tblimport and the new table to show all the records from tblimport
> with an assigned number)

Once you've added those two fields you will need to input the region
A-Z. Unless this table will grow. Then you might want a method (a
form) to assign regions and region numbers. Or use the form I describe
below.

I would create a continuous form. Click Forms/New/FormWizard. When you
open this form it should display all records.

Maybe have the sort be all records without a region or number float to
the top. This way you can add records and add the regions at that time.

I probably would have two combos or two listboxes in the form header;
one for region the other for region numbers. With a combobox, you can
have one region, one region number, or a combination of both. With a
listbox you can have multiple combinations of regions and region numbers.

Here's an example of code in the AfterUpdate event of a combo box.
Sub Combo_AfterUpdate
'put in all combobox AfterUpdate events
SetFilter
End Sub
Sub SetFilter()
Dim strF As String

'get filter for region
Select Case UCase(Nz(Me.ComboRegion, " "))
Case "A" To "Z"
strF = "Region = '" & Me.ComboRegion & "' And "
End Select

'get filter for region number
If Not IsNull(Me.ComboRegionNumber) Then
strF = strF & "RegionNumber = " & _
Me.ComboRegionNumber & " And "
End If
Select Case UCase(Nz(Me.ComboRegion, " "))
Case "A" To "Z"
strF = "Region = '" & Me.ComboRegion & "'"
End Select

'remove And if there's a filter
If strF > "" Then strF = Left(strF, Len(strF) - 1)

Me.Filter = strF
Me.FilterOn = (strF > "")
End Sub

Now when somebody makes a selection from either combo box the form's
recordset is filtered to those that meet the region/number filter.

You can add other controls to add additional filters to the form and
limit your need to create queries.

>
> I simply cannot see how to do this! I'd appreciate somebody setting my
> brain straight. Thanks,

Be Yourself
http://www.youtube.com/watch?v=xzNYfMTWpVs