• 0

Database creation, Access vs SQL


Question

Not even sure where this goes (bad I know :D)

Ok so basically boss wants to create a database of clients and their the jobs attached to them so that way we can search say:

How many jobs were done for Capital Bank between the years of 2009 and 2012 that were $5,000 or more.

I've exported information from Quickbooks to get customer info. I can export all the jobs done as well to an excel sheet but can't figure out how to match up client with jobs in an excel sheet or access database. I'm not a software guy but apparently I know the most about this stuff. :ermm:

We have a SQL server also running for a few other programs so that's why i put it as a option as well.

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

Well for the data you want.. Access and MySQL are both viable, and borth work. You would just write a parser to Parse CSV outputs of your QB or Excell Sheet and import them.

For the linking you would just assign each client with an ID. Then you would assign each job the clientID that did it. It may require some manual adding in some cases, but that's how I would do it. You would have two tables

Customer and then Job customer would hold all the customer info with a unique customerID. Job would hold all the job info (Cost, Date, etc) it would then be paired up with the customerID from the customer table where the customer is the one that did the job or had the job done (depending how it is logically)

Link to comment
Share on other sites

  • 0

Not even sure where this goes (bad I know :D)

Ok so basically boss wants to create a database of clients and their the jobs attached to them so that way we can search say:

How many jobs were done for Capital Bank between the years of 2009 and 2012 that were $5,000 or more.

I've exported information from Quickbooks to get customer info. I can export all the jobs done as well to an excel sheet but can't figure out how to match up client with jobs in an excel sheet or access database. I'm not a software guy but apparently I know the most about this stuff. :ermm:

We have a SQL server also running for a few other programs so that's why i put it as a option as well.

If the data has to be accessed from multiple computers, SQL is the better option IMO. If it is all on a single computer, Access is easier to deal with when creating reports. That's just my personal experience though. Also keep in mind that Access databases have file size limitation so depending on usage, that may be a factor.

Link to comment
Share on other sites

  • 0

Either, or more importantly, what you and your co-workers are more comfortable with.

Also, if people don't have Access, it's easier to get everyone hooked up to an SQL server.

Link to comment
Share on other sites

  • 0

Either, or more importantly, what you and your co-workers are more comfortable with.

Also, if people don't have Access, it's easier to get everyone hooked up to an SQL server.

This is 100% true. However Access works well if everyone is connected, no need for a deadicated mysql server. An access MDB can be dumped on a share on some server and everyone can access it. That's what they do where I work, though I am working on transitioning it all into MySQL for speed and space related reasons.

Link to comment
Share on other sites

  • 0

Well for the data you want.. Access and MySQL are both viable, and borth work. You would just write a parser to Parse CSV outputs of your QB or Excell Sheet and import them.

For the linking you would just assign each client with an ID. Then you would assign each job the clientID that did it. It may require some manual adding in some cases, but that's how I would do it. You would have two tables

Customer and then Job customer would hold all the customer info with a unique customerID. Job would hold all the job info (Cost, Date, etc) it would then be paired up with the customerID from the customer table where the customer is the one that did the job or had the job done (depending how it is logically)

Ok sounds good then. In Access so far I've created the Customer Info and Access (2010) gave it a "ID" field - up to 770 right now. Some customers have over 200 jobs each so the "jobs" table will be much larger I assume. I'll have to export that to a CSV tomorrow and then that be manually tying to the ID field of the customer I guess. Sounds like that could work but probably take forever.

Now the question is how about adding stuff, would just have to manually update it or how would that work?

I noticed Access 2010 has a Export to SQL server thing (or even Sharepoint) I was thinking I could do Access and then push it to the server. This dBase wont even be used much IMO, I think it's just something the boss wanted to do and couldn't find a easy fast way to do it so he thought this up.

Thanks for the laughs too cralias :)

Link to comment
Share on other sites

  • 0

Ok sounds good then. In Access so far I've created the Customer Info and Access (2010) gave it a "ID" field - up to 770 right now. Some customers have over 200 jobs each so the "jobs" table will be much larger I assume. I'll have to export that to a CSV tomorrow and then that be manually tying to the ID field of the customer I guess. Sounds like that could work but probably take forever.

Now the question is how about adding stuff, would just have to manually update it or how would that work?

I noticed Access 2010 has a Export to SQL server thing (or even Sharepoint) I was thinking I could do Access and then push it to the server. This dBase wont even be used much IMO, I think it's just something the boss wanted to do and couldn't find a easy fast way to do it so he thought this up.

Thanks for the laughs too cralias :)

You could do manual entry right inside access. However Access also supports making MDE files (Basically access programs) using Visual Basic for Applications, so you could write a nice gui for imput. I use C# for all my coding at work using OLEDB connectors.

Link to comment
Share on other sites

This topic is now closed to further replies.