[LONG] Beers in an Access Table

[Eric]

Hi everybody,

Is it possible to build a form looking like an Excel one ?

I explain with an example.
I want to have a report concerning number of beers drank
by countries.
I have 3 tables :
- country{ ID, countryName}
- beer{ID, beerName}
- result{ID, countryID, beerID, quantity}

So here's the table, that I need to fill in real time
(really looking like an Excel one):

USA UK Germany
Carsberg 4 10
Bud 10
Kilkenny
Guiness 5 6 7
Kronembourg

The problems are :
-the table will not be filled at a time, so I need to be
able to update each field
-the countries and beer type may be added or deleted at
anytime (implying deletion from values of the column or
line)

Is it possible to build such a table with Access? I think
that the main problem is that I do not know how to show
the empty fields from the table (because they do not exist
in database yet).

In my example,
Country contains USA, UK, Germany
Beer contains Carsberg, Bud, Guiness, Kilkenny, Kro.
Result contains (Carsberg, USA, 4)
(Carsberg, UK, 10)
but nothing concerning (Carsberg,
Germany, ???), as no value has been entered, so I can't
display it on the form.

(thats why I can't build the form, at the beginning, there
are no value entered at all !)


One solution could be :
Each time I insert a Beer, then for each country I
automatically insert an empty row in the result database.
Ex : I insert "Fosters", then I automatically insert
(Fosters, USA, 0)
(Fosters, UK, 0)
(Fosters, Germany, 0)

and same if I insert a country... This seams very
complicated, isn't there a simplest way to do?
If this is the right solution, can someone give me some
advice? (is that a macro, is that Visual Basic, where
could I find some information?).


Thanks a lot

Eric

PS : of course, I'll be happy to offer a bear to anyone
able to help )

 

[Ed Lillis]

I assume that your main form is based on the 'result' table and the other
two are for lookup combos in the result form and each of the IDs are
autonumbers.

If you use the datasheet default view then you can add new records by
directly adding values for the country, beer, and quantity (one record at a
time).

ed

 

[Eric]

If you use the datasheet default view then you can add
new records by

directly adding values for the country, beer, and quantity (one record at a
time).

Sure, but what I wanted was to have a global view of the
table to update in one click. (on a large table, it can be
very long to add each record one by one, selecting the
contry, then the bear, then entering the value).

Anyway thx for your help !

Eric

 

[Ed Lillis]

It is difficult to have a database without entering data, however, if you
just want to enter the country and beer once you could build a query (and
make it an update table query) that contains the country and beer tables
with no link between them. That way each beer will be represented by each
country.