Database Design Help

[binary intelligence]

Hello everyone. I am currently working on a project and need some help.

I have 4 tables that have 5 fields…

Table 1

Tbl1_id (autonumber) primary key?
Date (date\time)
Tbl1 list box 1 (text)
Tbl1 list box 2 (text)
Tbl1 obj1 quantity (number)

Table 2

Tbl2_id (autonumber) primary key?
Date (date\time)
Tbl2 list box 1 (text)
Tbl2 list box 2 (text)
Tbl2 obj2 quantity (number)

Table 3

Tbl3_id (autonumber) primary key?
Date (date\time)
Tbl3 list box 1 (text)
Tbl3 list box 2 (text)
Tbl3 obj3 quantity (number)

Table 4

Tbl4_id (autonumber) primary key?
Date (date\time)
Tbl4 list box 1 (text)
Tbl4 list box 2 (text)
Tbl4 obj4 quantity (number)

Every table I have created so far gets all of its information from the form
the users fill in accept for the id of each table. Right now the users have
to type in the date. Is it possible for the user to choose a date from a
calendar? Or atleast format the date field to display a date as day month
date? I tried to mess around with the formatting but the ddmmyyyy is not
workin. I have learned how to display the information for each field in a
combo box but how do take a say as an example a field that has 3 records used
in a drop down box and record the input selected by the user from the form in
2 different tables? Also how do I make sure that all the data on the form is
kept in the same record if the data is recorded in different tables depending
on what the users selected from the drop box. Am I supposed to link the ID
tbl_id fields for each table or what? So many questions and I even have a
book and still can’t figure it out. I guess that is because the book just
tells what each feature is and not really how to bring data together (I could
be missing something) using all the features of Access. Worst part is that
this is just the tables. I still have to create queries that give the total
for the quantity of items and type of items of each table. I know this plea
for help may seem a lil vague but I cannot explain in great detail what the
data is for sake of security but any feedback would be greatly appreciated.

 

[JulieD]

Hi

i'm a bit confused by your table structure ...
i'm not sure why you didn't go for a two tables with the following structure

the main table:

ID (autonumber) PK
Date 'but remember never use "date" as a field name
Text1
Text2
Object Type 'this field would have a related table with your object1,
object2, object3, object 4 as data in it - your form can show a combo box to
pick the type from
Object Quantity

the lookup table for object type

ObjectType (text) PK

or if you want

ID(autonumber)PK
ObjectType (text) 'with no duplicates allowed

....
this would make querying a lot easier.

i know this doesn't answer your date entry question but does it make a bit
more sense on the querying side?

 

[PC Datasheet]

If you would like some confidential help, contact me at my email address
below.

 

[John Marshall, MVP]

If you are going to spam the groups with solicitation for work, can you at
least post your prices and your real name?

All the good posts you do are completely trashed by your insistence on using
these forums to troll for work.

John... Visio MVP

 

[PC Datasheet]

And what did you do here to offer any kind of help to the original
poster.....?

Steve
PC Datasheet

 

[BruceM]

He headed off any impression the original poster may have had that you were
offering your help for free. Why don't you post some free samples at your
web site, and provide a link to those? Somebody following the link could be
offered other choices at the web site, including consulting services. As
long as the samples are available with no strings attached there would be no
cause for objection. Others who post here do just that sort of thing.

 

[binary intelligence]

I think I understand what your saying to a certain extent. Lemme give
another example of what I'm trying to do here...

Ok lets say I am trying to create a book database in which a user enters the
date, chooses from a drop list the main genre the book belongs to, then the
user chooses from a droplist the sub-genre the book belongs to, and the
quantity of books being reported. Ok...As the database stands right now I
have a table for the main genre that has a column called book_id and book
category. To explain why I created this table...I thought I would use this
table for the droplist the user chooses for choosing what genre the book
belongs to. For each genre I create tables that have a column named after
the genre name (say horror) and a primary key called say horror_id. This
would be the droplist for the sub genre in which a user would choose which
type of sub genre the books belong too. Ok what you're saying is that I
should have one table for all the sub genre data for each of the main genres?
For example lets say I had 5 sub genres for horror books and 4 sub genres for
romance. I would put both these groups in the same table just them in
separate columns? Your help is very much appreciated. Hope my example was
understandable.