Edit multiple tables with one form per record

[ricky.agrawal]

I'm really not sure how to go about this in Access. What I've created
is a table for each location. Those tables are identical in format but
different in information.

The tables are named after key points such as the store number and the
store ID. The fields of those tables are generic fields such as sales
per day, bank deposit and what not. The first field for each store
table is the date and I've set that as the primary key as one store
cannot have more than one sales data per day.

The problem I have encountered is that I cannot seem to create a form
that will have a data entry box called DATE in which I enter in the
date.
Along those lines, I cannot seem to have some thirty tables or so have
strictly the records come up which correspond to that selected date.
That way I can enter in information corresponding to 9/26/2005 for all
thirty stores (or however many store tables there are).

I tried to start simple and create a form that will let me edit
multiple tables, but when I copy over the boxes from one form to
another form, it seems to revert all the entry boxes to the first
table. I am confused on how to proceed and complete this form in MS
Access.

[Allen Browne]

Ricky, this is not how you create a relational database.

You need a Store table, with one record for each store. Fields:
StoreID AutoNumber or unique code for the store. Primary key
StoreName Full name of this store.
Suburb Where the store is.

Then you need a Sale table that contains the sales info for all stores. It
will have fields:
SaleID AutoNumber (primary key)
StoreID Relates to Store.StoreID (which store this entry is)
SaleDate Date/Time The date for this entry.
Amount Currency The amount of sales for this store on this date.

You can now create a form bound to the Sale table. It will have a combo box
for selecting the store.

If you prefer, you could create a main form bound to the Store table, with a
subform bound to the Sale table. The subform would show the sales for that
store (one row for each date.)

BTW, don't call a field Date: that's a reserved word for the system date.

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ri***********@ gmail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

I'm really not sure how to go about this in Access. What I've created
is a table for each location. Those tables are identical in format but
different in information.

The tables are named after key points such as the store number and the
store ID. The fields of those tables are generic fields such as sales
per day, bank deposit and what not. The first field for each store
table is the date and I've set that as the primary key as one store
cannot have more than one sales data per day.

The problem I have encountered is that I cannot seem to create a form
that will have a data entry box called DATE in which I enter in the
date.
Along those lines, I cannot seem to have some thirty tables or so have
strictly the records come up which correspond to that selected date.
That way I can enter in information corresponding to 9/26/2005 for all
thirty stores (or however many store tables there are).

I tried to start simple and create a form that will let me edit
multiple tables, but when I copy over the boxes from one form to
another form, it seems to revert all the entry boxes to the first
table. I am confused on how to proceed and complete this form in MS
Access.

[ricky.agrawal]

Perhaps I was breaking down the information too much.
If I combine all the store tables into one table, then I have gone
around the issue of not being able to edit multiple tables with a form
because I'll have my sales information in one table.

But that still brings me back to an original problem I could not figure
out how to solve.
In the form, it displays all entries in the table. I just want to add a
new record.

Perhaps something like this..

--- DATE ---
STORE ID^1 | SALES^1 | DEPOSIT^1
STORE ID^2 | SALES^2 | DEPOSIT^2
STORE ID^3 | SALES^3 | DEPOSIT^3
STORE ID^4 | SALES^4 | DEPOSIT^4

When I input data, then whatever I submit with DATE being 9/25/2005,
all the records submitted will have that date in Field1.

Am I looking at the entire aspect the wrong way?

[ricky.agrawal]

Allen, thanks for your reply.
I do have a Store table, I have called it the Store Master Information
where I have the following fields.
Store Number | Store Address | Store Phone Number| etc,.

I understand what you said with the Sale table, however I do not
understand how I can create a form bound to the Sale table.
I don't need to show sales for more than one date at a time. I just
need to be able to enter the information for all the stores in one
screen/form to make it easy to enter information.

Once again, thank you for your help, I appreciate it and now I know
that going to a table per store solution was incorrect. I will have a
Store table which has all the stores and their corresponding
information, and then a Sales table which will have all the sales
information.

I will try to search in my Access book about bounding.

[salad]

ri***********@g mail.com wrote:

I'm really not sure how to go about this in Access. What I've created
is a table for each location. Those tables are identical in format but
different in information.

The tables are named after key points such as the store number and the
store ID. The fields of those tables are generic fields such as sales
per day, bank deposit and what not. The first field for each store
table is the date and I've set that as the primary key as one store
cannot have more than one sales data per day.

The problem I have encountered is that I cannot seem to create a form
that will have a data entry box called DATE in which I enter in the
date.
Along those lines, I cannot seem to have some thirty tables or so have
strictly the records come up which correspond to that selected date.
That way I can enter in information corresponding to 9/26/2005 for all
thirty stores (or however many store tables there are).
I'm sure others will point out that DATE is a reserved word and should
not be used. Can you not name the field StoreDate or something similar?

I don't know why you don't have 1 table instead of 30 stores. It would
make things so much simpler. What happens if one of those stores
closes? What happens if you you add another store? Then your system
becomes less usable.

You should be able to select a store, a date, and filter records that
match that store id and date.

I tried to start simple and create a form that will let me edit
multiple tables, but when I copy over the boxes from one form to
another form, it seems to revert all the entry boxes to the first
table. I am confused on how to proceed and complete this form in MS
Access.

A form can be unbound and bound. If unbound, no table is associated
with the form. You would update the information when a Save button is
pressed. You would fill in the record's data when you go to a new
record. This is more work than a bound form.

A bound form is associated with a table. Let's say your form's
RecordSource (property sheet, data tab for the form) is
Store1Table
or
Select * from Store1Table

When you move to another store, you need to switch the RecordSource.
Let's say it opens to Store1Table. You now select Store2Table from a
dropdown. You would then do something like
Me.RecordSource = Store2Table
or
Select * from Store2Table

Your life can be complicated or uncomplicated. You can make a mountain
out of a molehill or climb real mountains. You might want to consider
changing direction to making your life easier so you can tackle the real
problems that will confront you.

[Allen Browne]

Regardless of what interface you choose, you will need all the sales info in
the one table.

There is no problem with entering the same date on several rows, for several
stores.

If you want the date text box to default to today, just set its Default
Value property to:
=Date()

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ri***********@ gmail.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

Allen, thanks for your reply.
I do have a Store table, I have called it the Store Master Information
where I have the following fields.
Store Number | Store Address | Store Phone Number| etc,.

I understand what you said with the Sale table, however I do not
understand how I can create a form bound to the Sale table.
I don't need to show sales for more than one date at a time. I just
need to be able to enter the information for all the stores in one
screen/form to make it easy to enter information.

Once again, thank you for your help, I appreciate it and now I know
that going to a table per store solution was incorrect. I will have a
Store table which has all the stores and their corresponding
information, and then a Sales table which will have all the sales
information.

I will try to search in my Access book about bounding.

[ricky.agrawal]

Setting the default value property to =Date() was a fantastic idea!
Thanks!!

I'm sorting through some help files and google to learn more about
bounding. I think I've gotten it down.

I've created a form with the following entry boxes.

STORE ID | SALESDATE | GROSS SALES | DEPOSIT

Now I have created a lookup so the STOREID is a drop down box.
SALESDATE is automatically entered in with todays date.
GROSSSALES and DEPOSIT are both currency.

What do I have to do to make it so that only those records with todays
date (or a specified date, I might try to figure out how to turn it
into a drop down box) are shown?
I just did a test and it shows ALL records that are in the Sales table.
That could get confusing.

[ricky.agrawal]

Salad, thanks for your reply!
I have created one table rather than 30 or so for each store. It is
much simplier to have one table for multiple reasons.

I think I have figured out a way to have only specified dates appear.
To use the filters!

I can simply apply a filter of 9/25/2005 and only those records with
those corresponding dates will come up!

The only problem I can see with what I have created so far is skipping
a store. However I can run a query later on to display a list of the
stores and their latest data entry. I'll have to figure out how to do
that (I haven't even look yet).

Or should I create a form with all thirty stores information displayed
without the STOREID drop down box so that folks know which stores have
their information entered in or not?

[ricky.agrawal]

Salad, thanks for your advice! I did move to having all the stores in
one table. It is truly much simpler.

Now that you bring up the issue of clones, is there a check I can
install that will prevent a duplicate record from existing using the
following criteria?

if((SalesDate && StoreID) exist)
DisplayErrorBox ;

Or something like that to prevent someone from incorrectly entering in
data for a store.

[ricky.agrawal]

I guess this brings me to my next step.

I understand that Access can store Number, Int, Currency and many more.
Can Access store Array's or perhaps CSV which I can tell Access to
GetChar until it hits a Comma and consider the order the characters
recieved as one type of object?

I think that would be really easy to use form wise, and report wise as
well as storage wise.

The problem I've encountered is I can't edit all 33 stores information
at one time and move to the next day easily.