Query as record source for form

[Cordury]

2 Questions:

I have one form which requires a query to populate it. However it seems that there are more text boxes than query fields. I know that there are larger databases out there in Access than the one I am trying to create. How does one get around this issue without using just one table.

Since I am using a query to populate the form, I can not edit or change the records? I am obviously doing something very very wrong.

Basically, I have a database with store date. Some records are static, ie the Store #, Address, City, State, etc.. while others are not ie dates and such. I want to pull both the static data as well as the variable data to one form that can be edited in the form and saved over.

 

[fneily]

If I understand your rather strange form design, it seems you have a main table - Stores. Then there's a Transactions table - order dates, product, quantity, etc. So you have a one to many relationship - Store to Transactions. Your store table should have a StoreID as a primary key. Your Transactions table has a primary key like TransID.
In your Store table, TransID should be a common field. That's the link between the tables.
Now you can create a form/subform - create a form for both Store and Transactions. Open the store form in design view and click the subform/subreport button on the ToolBox toolbar. Click under the last control on your form and the Subform wizard will take you by the hand and create the subform for you. You can now input multiple transactions per store on just one form.

Neil

 

[GingerR]

hi there.
1) dont understand your question. you can have whatever text boxes you want on your form. just delete the ones you dont want. how did they get there if you didnt want them??

2) just wondering this from our earlier conversations: do you have just one table with all data in it, and is some of the data repeated on multiple rows (i.e Address, City, State, etc) what you call 'static' data?

how you should have your db set up (normalized) is this (i'm just making up table names and stuff so you'll have to tweak):

Table1: Clients
Fields: ClientID (key)
Address
City
State, etc

Table2: Projects
Fields: ProjectID (key)
ClientID (comes from table Clients)

Table3: ProjectDates
ProjectID (from table Projects)
ProjectStartDate
ProjectEndDate, etc

i think table1 is what you are calling your 'static' data, and table2 is what you are calling your 'variable' data. this is how a relational database should be set up. once you start repeating data in a table, it's time for a new table.

THEN--for data entry and viewing, you will set up a form and subform. the main form will be based on Project. it will show info from table Projects. the subform will be on table ProjectDates. if you make the master and child links between the main form and the subform, you will be looking at a screen that is concerned with Project 77, and all of it's dates will be on the same screen. do you know what i mean?

if you cannot figure out how what i am talking about pertains to your own data, then please supply details about your table structure (i.e. table names and field names). also tell us things like "A Project has only ONE CLIENT. A Project can have MANY DATES" and things like that. sounds like you could read up on 'normalization' which is the backbone of any relational db.

in reference to your earlier posts about importing in data, when you import you would add new clients to the client table, associate them with projects in your Project table, etc. not just dump it all into one table.

hope this helps--g

 

[Cordury]

Good Morning,
And yes I agree with you both that my db has some real issues. As I stated in another thread. I believe I am making this much more difficult than it needs to be. Here is what I am trying to do:
I work for rather large sports apparel company. Stores are constantly opening, closing, relocating to temp and or perm locations. I work in the Energy Management department. It is our duty to make sure that the stores have power, water, gas, etc. I am trying to create ONE database that will store (no pun intended) utility info, like the name of the Elec Co and the Account #, Water Co, Account #, etc.

The main problem I am having is that currently, this data is stored in many different places right now. I would like the end user to be able to pull up a store #, and see the address of the store, who currently provides power and what the account # is.

My main form is currently using a query that combines the table with the address and the utility information to populate it. However, using this method- the fields can not be updated. Nor do I have enough room in my query to populate every field.

Basically, the addresses are on one table- Utility info on another. Both tables contain a store #. I want to show all info on one form and all be able to edit that form.

I apologize about the confusion.

 

[Cordury]

Ginger,
Answer to your first question: I added all of the text boxes to my form yet was having difficulty populating them because the record source for the form is a qry that combines the store info with the utility info.

And in regards to your tables comment. I have it set up similar to your suggestion:

Table1: tblAddresses
Fields: Division
Store #
Address
City
State

Table2: tblDates
Fields: ID(Primary Key- May need to change this)
Store #
Possesstion Date
Construction Begin Date
Construction End Date
These 3 dates are repeated 3 more times,
obviously the field names have an appropriate #
as an extention.
The reason these dates are repeated is bc in construction, dates are cpnstantly changing and I wanted to keep a history of the original and revised dates.

Hope this helps clear up the confusion.

 

[GingerR]

1) suggestion for the "constantly changing dates":
do not have three sets of construction date fields.
better is to have just one set of date fields. that way you can have as many construction dates as you wish. then your data would like like this:

Store# ConstStartDate ConstEndDate
1277 3/18/03 3/20/03
1277 4/21/03 4/24/03
1277 5/18/03 5/22/03

you could even then have a notes field or construction project description field, and it would be tied to the construction done on those dates for that store. again, this way a store is not limited to three construction dates.

what does "Possession" mean? if it is the date your company bought the store, i suggest putting that into the STORE table, as it only occurs one time.

2) is there multiple pieces of UTILITY information for each store? i.e is there info about Electric, info about Water, etc? in that case maybe your form is built incorrectly. make a main form based on the store. then make a subform showing the construction dates .then another subform showing utility info. on the main form you can create a combo box and use the wizard and it will help you along in creating it so that you choose a store from the drop-down list and it moves to that record. then you'll see the store in (address etc) and embedded in that, a subform showing construction info and another showing UTILITY info.

 

[Cordury]

Ginger,
Thanks again for your response. And You are right, I may scrap the entire thing and start from scratch.

1)I like your suggestion regarding the dates, and I could just create a query to pull by store ID and populate all the dates.

Possession is when the company is allowed to enter the store, and these dates do change.

2) Yes, the Utilities will have many different fields (the name of the utility co, the account #, and if the 1st or final bill was received (Yes or No).

But I am not sure if a combo box is the way to go because there are eventually going to be a few thousand stores.

I am going to try to build a new form based on the store and try the sub form avenue. The end user will still be able to enter data this way? And will I still be able to import data this way?

Have a great weekend, Ginger and thank you.

 

[Cordury]

One more thing- Going back to an earlier response. You are right, I need to read up on normalization because I am all over the map with my crazy ideas.
Thanks again,
Max

 

[GingerR]

yes max that's how you'd enter the data. with the form/subform route.

1) the combo box/store selection: do you/the users already know what the store name or number is? if so then make it a text box. what i have done before is put on a combo box using the wizard and choosing the route about "I want to select something and then the form goes to that record". then i copy the code that Access generates behind the combo box, and apply it to the text box. so you put in #1233 and after you type that store number, the form moves to the record for that store.

2) importing...sounds like you have lots of different sources you are importing from. will you be importing forever? will the files from each source always be formatted the same (same columns in the same order, etc)? then you can automate the imports. also, once you have your data set up in a more normalized way, you can import in the newest data into a 'temp' table, then run append queries that pick out of each 'temp' table and move the newest data into the tables it needs to be in. at first you would do this all manually until you have it set up right: create any import specifications you need, set up some kind of process where the file is renamed to the same name and plunked into the same folder each time, write all the append queries you need...then in a macro or module (run from a button perhaps on an administrative form or run just from the module itself) you put in all of these steps so they will just run automatically for you. i.e.

Clear Temp Table 1
Import New Data From Excel Sheet 1 into Temp Table 1
Run Append Query that moves some data to a table
Run another Append Query
Run another Append Query

Clear Temp Table 2
blah
blah

so after a while, you can automate it.



oh as for Possession, i dont know if that should go into it's own table, or right into the STORE table. if you want to keep track of multiple possession dates then put it in its own table (because it's a 'one-to-many' relationship):

table name: PossessionDates
Fields: StoreID, PossessionDate

if you will just write over an older possession date with a newer one (bad if you want to preserve history) then put a single field right into the STORE table (Because it's a one-to-one relationship).

hope this gives you some ideas.

 

[Cordury]

Good Morning Ginger,

I just printed out your last post. I think it is going to be very useful. I thought about the db this weekend and definitley agree with your reccomendations. Since I will have data coming from mulitple sources (that should be in the same format each time), I will create a query to combine the data and then save the combined data to a table that will be the source for the form/subform. Right now, I have a query as my record source, this makes is challenging to add new records.

And I really like using the combo box idea to get the code needed to search for a record in a text box. Seems pretty obvious but I did not think of it (big surprise).

I am going to try and work on the db the majority of the day. Hopefully, I will make some progress. I will let you know how it turns out.

Again, thank you so much for your time.

 

[Cordury]

Ginger,
I have started from scratch. I have two main sources of data that I am attempting to combine.

table1- Contains all store in including dates which I eventually want to place all dates to another table.

table2- Store addresses.

I attempted an append query to combine the two tables. And it worked, sort of. Some records from table 1 do not have addresses yet and the append query is not pulling those stores or any of that information.

Table one also has the 3 dates that we discussed earlier. The users used to just over-write the dates and I want to keep a history. Is there a simple way to simply remove the dates and store # so I could save them on a seperate table or should I cut and paste those fields before importing the data to Access?

 

[Cordury]

Edit: Please disregard the 3rd paragraph. I ran a simple query from the 1st table and saved the results to a seperate table.

I am not so smart.

 

[GingerR]

doubt it that you are not so smart. you seem to be doing a good job getting in there are fixing up your db. i'm guessing that a lot of people, after someone here suggests they re-design their tables, blow it off cause they are used to how it is laid out already, or have so much other programming attached to it they feel like it's too much work. it's nice to see you are trying to move in the 'right' direction, even if it is a little painful right now. believe me, you will be much happier later!!

 

[Cordury]

Ginger,
Thanks for the vote of confidence. I agree, that I would rather do it the right way in development before it goes lives and spend the next couple of months trying to patch things up.


One more question. There are 3 main tables in this db:

table1) Store Info/Utility Info
Table2) Store Addresses
Table3) Dates (Possession, Constrcution Start, Const End).



Is there any way to populate the main form using more than one table and still be able to update fields? I want to combine Table1 and table2 in the form. And the only way I can come up with is to run a query, export the results of the query, and then change the record source of the form to the results of the query. There has to be an easier/better way.

 

[GingerR]

yes.

please give all details of your tables:
table name
all field names

a quick question: does Possession Date have anything to do with Construction dates?

for now forget about how you are going to import any data. we will deal with that later. once you have it all set up how you want it, that part will be easier.

how you set up the form depends on how you have your tables set up.
how they should be set up is this:



=============================================
table: Stores
fields: StoreID, StoreAddress, City, Zip

=================================
table: Utilities
fields: UtilityName
examples: Electric, Water

table: StoreUtilities (Combo of Stores and Utilities)
fields: StoreID, UtilityName (from table Utilities), UtilityCompany, UtilityContact, etc

==================

table: PossessionDates
fields: StoreID, PossessionDate
examples:
1 1/12/02
1 3/14/03

=========================

table: ConstructionDates
fields: StoreID; ConstructionDateStart; ConstructionDateEnd
ex: 1 1/15/03 1/21/03
1 3/18/03 3/20/03

=================================

form1: StoreMain
recordsource: table 'Stores'
form type: FORM/SINGLE FORM

form2: StoreUtilities
recordsource: table "StoreUtilities"
form type: datasheet

form3: StoreConstDates
recordsource: table "ConstructionDates"
form type: datasheet

form4: PossessionDates
recordsource: table: PossessionDates
form type: datasheet

======================

then open form 1 (StoreMain) in design mode
reduce it's size (not maximized)
click on one of the three last forms and drag it into the detail section of your main form.
do that for all three of the last forms.
each has the property Link Child Fields and Link Master Fields. for each, it should be StoreID. what this is doing is make subforms which are linked to the main form by the store ID.

================================
so do that, let me know when you're done.
you dont have to use my exact field/table names, but you have to split out all your data just about how i've written above, ok?

 

[Cordury]

Good Morning Ginger,
It is going to take me some time to create these new forms and I am only working half a day today.

The only way Possession Date is related to Construction Date is that the Construction Date is always later than the Possession Date. Construction can not begin until we Possess the store.


tblAlpha has about 30 fields right now, all may not be necessary. The fields in tblAlpha are:
ID (Primary Key)
Store # Project ID Gas Co
Unit # Project Type Gas Acct#
Address Project Manager Gas First
Mall Comments (memo field) Gas Final
City Account Status Water Co
State Electric Company Water Acct#
Phone Electric Account # Water First
Space # Elec First Bill (Y/N) Water Final
Elec Final Bill (Y/N) Sewer Co
Sewer Acct# Sewer First
Sewer Final

tblAddresses
Unit #
Store #
Mall
Address
City
State

tblDates
Store#
Unit#
Possession Date
Construction Date
Construction End

I have the 3 dates Poss, Const Date, & End repeated 3 more times with the appropriate externtion 1, 2,4. I know we discussed having this table repeat Store #'s every time the dates changed. So if the dates changed 3 times for store 5555. There would be 3 records for store 5555 in the table. But I was having issues with it saving over the old dates. And to be honest, the history of the changing dates may not be that big of deal considering how many other issues I am having.

I am not sure I am going to be able to use a table for specific utilities, if you are refering to all company names. Obviously it would not be a problem to create a table with four fields: Electric, Water, Gas, Sewer.

Thanks,
Max

 

[Cordury]

Ginger,
I have set up the tables you suggested except for the Utilities Table. I am not sure exactly what you are suggesting.
Thanks,
Max

 

[GingerR]

maybe i'm wrong: i thought you'd said earlier that a store can have many utility records, like

Store #1

Type Company Account Contact
Electric Puget Sound Energy 2348893 John Smith
Water King County Water 87CC562 Mary Jones
Gas Puget Sound Enerty GA84559 Tom Brown

etc

so since i thought that each store can have multiple utility records, the utilitites would go into a separate table:

table:Utilities

fields:
StoreID
UtilityType
UtilityCompany
AccountNumber
Contact
etc

if wrong let me know, or just ignore me )

 

[Cordury]

No Ginger, you are correct, (Good Morning by the way. A Most stores if not all have different utility providers.

So I will set up the table as follows:
Store ID
Elec Co
Elec Acct
Water Co
Water Acct
Gas Co
Gas Acct
Sewer Co
Sewer Acct

I also completed the forms you suggested yesterday (except for the utility). I dragged the Construction Dates form and the Possession Date form over the Store Form in design view. But I am having some difficulty find the child/master property. What tab is it on in the property box? And I am not sure how to define the form type ie Table Stores Form Type: Form Single Form.

Thanks,
C

 

[GingerR]

child/master:

in the main form design view, if you dont see the PROPERTIES window already, go menu VIEW+PROPERTIES.
click once on a subform.
tab = "ALL"
near the top should be Master Link Fields and Child Link Fields. it may have filled it in already for you.

as for your utilities table: if you are gonna set up the table like that, obviously each store will only have one record in that table. you may as well put those fields right into your main STORE table.

HOWEVER, i strongly suggest making it more like i have it. suppose some day there is a new utility type (i'm theorizing here). then you'd have to go add more fields, then go thru every query, form and report where these items are necessary, and add programming for those fields. or suppose you want to keep history, like account numbers change. perhaps you could add a date field to the table (my design) which indicates the date that account was activated. these things wont be possible or work correctly for you with your design.

i suggest this (and i may be wrong but this is based on my experience):

table: UtilityType (holds unique utility types)
field: UtilityType
sample data: Electric, Power, Water, Gas

table: StoreUtilities (combines store with utility data)
fields: as i have in earlier posts

then in your input form, you would have a subform based on StoreUtilities table. it will just look like a list. the first field (UtilityType) would be fed from table "UtilityType" so the user can only choose certain things. that way you/they will not spell "electric" four different ways (Elec, elect).

advantages:
1) later on, say someone wants to search by just putting in a store ID or choosing the store from a combo/dropdown box. the info they want is the phone number and acct number for the electric service. they just choose ELECTRIC from a combo box. hit a button, there's the number.

2) say you will end up having a one-page report which shows all details of a certain store. with my method you plop a subreport on the main report much like you do with a subform, and if there are more accounts added that subform portion just grows longer. as i said earlier, with your method you have to go place each field on the report yourself, so if you then add Electric2, ElecAccount2 etc you then have to go tweak your report design. same with forms.

anyhow, you can do as you wish of course. i'm just suggesting this based on my experience and how i think you may run into problems later on. dont worry if you dont want to do what i suggest - i dont care and will still help with other issues ))

g