Need more fields in a database. Ran out of space. How can I?

[Guest_imported]

I am working on a database & I have ran out of space. I have no more fields available & I need to somehow make the database bigger, so I would like to find a formula that I can type into a field in 1 database that would retrieve data from another databases (or between a Spreadsheet & 1 Database would be even better)
I’m using ~Microsoft Works (2000) Database~ (I also have Microsoft Works Spreadsheet)
Here is a couple of examples to help clarify what I am looking for.
Example:
Within one database, I can type in a simple formula such as =Field1 ...If this simple formula is entered into any field in a database, it will display, in that field, what ever is in Field1
I have no trouble with this part. I can have it do every function I need it to do & more, like Add or Subtract to other amounts, Calculate percentages, Auto Sum, as well as 'IF statements' etc....etc...etc...
* * * * ~ NOW ~ * * * *
What I would like to do is very similar to the example I have used above, but what I would like to do instead, is enter a formula in a Field of one database, that would retrieve data from a field of a whole other database (it would be even better if it could get data from a field of Spreadsheet instead of a second database).


Example: Database #1 & Database #2 or (Database #1 & Spreadsheet)

I want to put a formula in a field of Database #1 that would get (& display) the data from a field in Database #2 (or from a Spreadsheet. ..It would be better if I could get the information from Spreadsheet, if possible, this would give me endless space)
In Database #2, I should be able to type, in one of the fields, a formula that will allow it to get the data from another database (or spreadsheet). I know this is the wrong formula (because I’ve tried it & dozens of others trying to come up with the right one) but in theory it should look something like this =Field1, Database #1
I have been working on this database for quite a while & was doing great, until I ran out of space. I know there are other types of database programs available like ~Microsoft Access~ for example, but I am comfortable using Microsoft Works especially in the Design Mode.
I need to be able to design several different forms with a specific look ....Example: Invoices, Work Orders, Purchase Order forms, etc... I have it now so it will take customer information & information about the product purchased & have all the proper information be printed in the proper spots on each form. I even have another form that tracks our inventory & shows us our profit & how much we need to reinvest as well as what to buy to replace what we have sold, as each sale happens.
I don’t like the pre-generated, generic forms that come with other database programs (example: Quicken). They never seem to be laid out properly for the applications we need & they don't have the look we are wanting.
So, I was hoping someone out there, maybe you, might know of a formula that would give it the right command that would give it the right path, of where to go get the data, when it is searching in another database (or spreadsheet)
If you have any other suggestions or you may know of a formula, could you please let me know. Or is there any other database programs out there, besides the one I’m using, that is easy to design my own forms, & would have enough available space(more fields) that would work for my needs.
The Microsoft Works (2000) Database program we are using has a maximum of 256 fields available in one database. I could use at least double that if possible. If I use Microsoft Works Spreadsheet just to input our product data & just use Works Database to get the data (& do the necessary functions we need) it probably has 10 times the space or available fields to input information, If I can only figure out a command, or formula, to enter into a field in a database to be able to go get the information from a field from Spreadsheet I would have it made. If you can think of any other database program that is easy to design the forms we need, the only other feature that I wish it had was the ability to insert drop-down boxes in the fields where I can have pre-determined choices ( this would cut down on the amount of typing needed at the counter while our customers are waiting for some of our slower typing, computer illiterate staff to type information in the required fields. With Drop-down boxes it is more point-&-click with the mouse. Now I'm getting too fancy. I would be content with a simple(or even complicated) formula I have described. Thanks again & please HELP ! .... LOL
My specific application is being used at our counter as an invoice that we give to our customers when they purchase something in or store. I have it set up to be able to print an Invoice as well as a separate Work Order & if needed a Purchase Order for restock of what ever items where sold. It works great except we have added more products to our inventory & I need to have more fields in the database to enter the new products. Any help would be appreciated

I've tried to explain above, what it is I'm trying to accomplish. Please excuse me for repeating myself in a few spots, but I was trying to describe it, enough different ways, that it will make sense to you. I know very little about programming, so if this is something you have the answer for, could you please try to be as specific as possible so that I will be able to use the information you provide me with. Thanks ... Dan

 

[pipk]

I think you need to go back to square one and design your database from scratch.

From the seemingly endless ramblings above it is hard to ascertain whether there are column limits placed by works on your table or whether it is physical space i.e. memory that is lacking.

If it is the first option then obviously you need to look at normalising your database data, this will naturally give your several tables that any database will allow joins between tables.

Other than that I think you will have put most people off this thread by the sheer volume of stuff you have written, no one is going to want to read it all, I know I certainly haven't. Try and be more succint and to the point it will be more helpful to us poor souls.

 

[sgaphotog]

I'm not familiar with microsoft works databases as I, when working with microsoft stuff, usually work in Access, but if you can create a link inside the works database to the external spreadsheet (the spreadsheet will need to be in the format of a database table of course with the first row containing field names etc...) and then if the database program supports writing queries, you can tie the works database table to the spreadsheet data via a foreign key. You may run in to the same field count dilemna though as queries usually only support the same number of fields as normal tables do, but you can then use the query to provide the data to the form you are using thus connecting the 2 separate sources together.

Your best bet, as the first responder mentioned, is to re-design your table structure and normalize your data. I just recently ported a fairly flat database shema to a more normalized version of the same application and it not only is less complex to work with the data, but in many cases the code runs up to 100 times faster.

Good luck!

 

[Monguse]

You are limited to 256 Fields per table not database this is a major differance, just creater another table and pull data from either or both. "The beauty of the second amendment is, that it will not be needed until they try to take it." - Thomas Jefferson

WebMaster:

http://www.firearms-for-us.com

 

[mattKnight]

I really am confused!

I admit, I didn't read the *whole* post, but I could do with enlightenment, from people who are more knowledeable reagrding Works databases.

Is works relational? ( iassume it must be, otherwise people wouldn't be talking about normalisation)

Does DJS43 mean table when he says "database", record for "field" and field for "column"

It looks to me like this is s "relatively" easy stock control POP ans SOP application, using a flat database. I agree, with almosrt everyone else: Redesign/normalise data - it will save time, effort, tears, blood and disk space!
If that fails, try using a different DB engine (Access or SQL)

Matt