Updating various tables from 1 form

[montygee]

I need to be able to update different tables with same data inputed through a form. Can anyone help solve this. I have spent 3 long days trying to figure out.
(You may notice that I am new to Access)

Thanks,
Montygee

 

[JeremyNYC]

MG,

Can you tell us a little bit about why you want to do that? It sounds like your basic table structure may be a little unorthodox, and fixing things at that level (if they're wrong) will make a HUGE difference in getting your database to do the things you want it to do.

What are your tables and fields? What is the function of your form?

If you get back with these answers someone will surely be able to help you out.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[montygee]

I am trying to set up a query that compares sales from different stores that opened in different months. Therefore, when I sort in my query to include only those units that were open in say January, when I use the same filter in Feb, it reduces the unit again if the store opened in Feb because there is a restriction in January. Don't think that made sense.

Jan'02 Jan'01 Feb'02 Feb '01 '02vs'01
Unit A open open open open (J+f)/(J+f)
Unit B open close open open (f)/(f)
and so on for the year. Essentially, I want to total all open months for each unit and compare against the same units for the same months as last year.

Thanks for any help

 

[JeremyNYC]

MG,

As I said, this sounds a little complex, and sounds like your tables may not be soundly built. I don't think I'll be able to help much without the information I asked for the first time around.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[montygee]

Thanks for the response. Let me answer your first question.

I have a table called Sales. In this table I have fields named
Store #
Region
Comparible Month Y/N
Jan'03
Jan'02
Comparible Month Y/N
Feb'03
Feb'02
Comparible Month Y/N
Mar'03
Mar'02
etc through Dec.

My form simply is the vehicle to input the data for the above by the end user.

Thanks again

 

[CJTyo]

Montygee,

What are the other tables? You only listed one. And I've some thoughts about that one as it's listed, but let us see the others too...

C
*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[montygee]

It's the only table I have

 

[JeremyNYC]

MG,

You'll definitely want to straighten out that table. Do a google search for "data normalization". The first three rules are all you really need to know, but once you get those thoroughly digested, you'll see why your table structure is making things difficult for you.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[CJTyo]

Oh. Your headline said update various tables from one form. Well, if you've only got one table you've got problems.

You should have, to begin with, at LEAST three tables that I can see (maybe 4 if you want to put all your people together in one and use an ID to reference them as needed). Maybe something like this:

Stores
------
StoreID (your primary key, Access autonumber)
Store# (your internal identification number)
StoreName
RegionID (links to RegionID in the Region Table)
...other relevent fields (location, manager(ID?), whatever)


Regions
-------
RegionID (your primary key, Access autonumber)
Region# (your identification number)
RegionName
...other relevent fields (if needed)


Sales
-----
SalesID
StoreID
SalesMonth
SalesYear
SalesAmount

Then link the IDs in your Relationships and you're good to go on queries and reports to pull and compare Sales data.

I hope I didn't miss anything here and covered all your fields.

C

*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[JeremyNYC]

C,

Why would you use two fields to store year and month? I assume that if you do this, you use numeric fields with rules about min and max values in them, at least? But what's wrong with a date field nad storing the first moment in the month?

J =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[CJTyo]

J,

I was just going down his list of fields... I'd use a single month/year entry myself.

I'm going back and forth between these Forums and working my way through 'Access 2000 VBA Handbook' to teach myself VBA. I'm probably not thinking clearly. Maybe it's time for lunch...

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[montygee]

Thanks guys. I think I will make some improvements to my table. You see, the table was created by importing all the data from excel spreadsheet.
But the real problem I can't get is this:
List on 1 report or query every store that was open for the same months as prior years. For example, Store #12 opened in June 2001 and store #13 opened in September 2001. Stores 1 -11 were open the entire year. Now when you query sales from the table and restrict to open stores by month, stores 12 and 13 will not show up because they have been filtered out.

Thanks again

 

[CJTyo]

montygee,

But Stores #12 and #13 weren't open in prior years so they shouldn't show up, right?

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[montygee]

They should when you are comparing 2003 vs 2002. This year, those stores will be open the entire year but only 6 months and 3 months respectively last year. Therefore, on my report I want store 12&13 to show 0's until they have a comparible month (month against where there are sales to compare - June vs June)

 

[CJTyo]

montygee,

How are you filtering/comparing them? Can you post your code.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[montygee]

Field:Store# |Region|Open/Close|JanSales|O/C |FebSales|
Table: Sales | Sales|Sales | Sales |Sales|Sales |
Sort:
Show: yes | Yes | NO | Yes | NO |Yes |
Criteria: | | "Y" | | "Y" | |

 

[CJTyo]

montygee,

So, for the months you're looking at when in 2002 the stores were NOT open the Open/Close would be a NO. Therefore, your Criteria of YES would eliminate them, even though they will be open in 2003. You're requiring a Y for both years.

You need to get the data broken in to different tables and then this will be a LOT easier for you.

C
*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[montygee]

Thanks

 

[CJTyo]

montygee,

No problem. If you need help with the normalization just post back. And after that, the comparison query.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.