Problem with MS Access for Office 2000

[morea]

Okay, so I've been working on setting up a database in MS Access on and off for about a month. I set up my tables and queries with no trouble, defined the relationship between the tables (not sure I did that exactly right...) and then built a form to generate invoices for me to print and send to freelance clients.

Problem is this: at this time I only have three clients listed, and between them have about a dozen 'invoices'.
When I go through the form, it only recognizes the customer numbers and lists three records, instead of recognizing the invoice numbers and listing all 12 records.

I think that it has something to do with the way I defined the primary key and foreign key in the form, or else with the way that the relationship was initially defined. Either way, I'm stuck, and the form isn't working the way I need it to.

Anybody have any ideas? (I hope... I hope... I hope!!!)

The more people I meet, the more I love my cats.

[PrintDriver]

I only know Filemaker.
Is the Primary key and Foreign key something like creating a Portal in the client database to a separate data base that stores the invoices?

PrintDriver is a large format digital print dude. His advice/opinions may not apply to the 4color/offset/web world of printing

[morea]

I think so... the primary key has to be defined in each table, then when you define a 'relationship' between two tables you have to pick one to remain the primary key and the one from the secondary table becomes the 'foreign' key...

I think that somehow I got them reversed and that's why I can't pull all the invoices. Unfortunately, I'm not sure how to correct this! I guess I'll have to try to redefine the relationship and go from there!

The more people I meet, the more I love my cats.

[PrintDriver]

Didn't Subdriverswife know someone who did those Access databases?

PrintDriver is a large format digital print dude. His advice/opinions may not apply to the 4color/offset/web world of printing

[C.E.]

it was me who was trying to do one and I'm about in the same situation Morea is in !!! I can't figure out how to get the primary keys and the tables aligned right in order to be correct.... unfortunatley I've had alot of crap going on in my life as of late and so I've pretty much put that project on hold... maybe i can start messing with it again soon.

[torque2k]

Did you guys ever get this sorted out? I had built an invoicing system off Access 1.0, way back when, and I remember part of the fight I had with it was understanding the one-to-one, many-to-one and one-to-many relationships when dealing with Primary keys. I think I ended up figuring it out by looking closely at how the tutorial DB was built (was it North Winds Co.?) and adapting that style. I had never used FoxPro before that, just Filemaker when it was flat-file. I never did pick up the best way to build relational DBs...

/plug mode on;

That said, I've actually started reading a great book about building relational databases correctly, and it clears up SO much I've been doing wrong over the years. It's called 'Database Design for Mere Mortals™: A Hands-On Guide to Relational Database Design, Second Edition' by Michael J. Hernandez (Addison Wesley, 2003), ISBN: 0-201-75284-0. It's garnered raves from Access and SQL DB programmers alike, and I'm pretty into reading it cover to cover now! If you thought learning about databases was boring, then, well... um... well, it kinda is. But this book does teach the basics very clearly, and since it's very brand-agnostic, you'll be able to use what you learn in Access, Filemaker Pro, Oracle, Progress, MySQL, etc.

/end plug mode;

But seriously, take a look at that North Winds Trading Co. sample DB that comes with Access. I think you'll 'get it' once you look at how they did it.

I'm a computer systems consultant and technician, but my business is localized to West Michigan.
However, feel free to check out my site, which always has new and interesting
articles on using computers:
CATALYST Techworks Consulting (http://www.catalysttechworks.com)

[morea]

Thanks torque... I haven't looked at in in a while... I'll have to check it out.

The more people I meet, the more I love my cats.